You could have used your original query listed above by simply adding the [ character before BETWEEN. See updated query below:

<![CDATA[SELECT * FROM table WHERE $X{[BETWEEN, date, BeginDate, EndDate} AND total > 0;]]>