This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

# Problems with a SELECT clause trying to get a query using BETWEEN for dates

The field TxnDate has a datetime type definition.

Why this works smoothly

``\$phql = 'SELECT SUM(Subtotal + SalesTaxTotal) as TotalVentas FROM invoice WHERE TxnDate >= "2018-01-01" AND TxnDate <= "2018-03-31"' ;``

And this does not.

``````\$iniFecha = '2018-01-01';
\$finFecha = '2018-03-31';
\$phql = 'SELECT SUM(Subtotal + SalesTaxTotal) as TotalVentas FROM invoice WHERE TxnDate >= ' . \$iniFecha . ' AND TxnDate <= ' . \$finFecha ;``````

And this does not too.

``\$phql = 'SELECT SUM(Subtotal + SalesTaxTotal) as TotalVentas FROM invoice WHERE TxnDate BETWEEN ' . \$iniFecha . ' AND ' . \$finFecha ;``

11.0k

I have found a solution is not the best but it is working

``\$phql = 'SELECT SUM(Subtotal + SalesTaxTotal) as TotalVentas FROM invoice WHERE CONCAT(YEAR(TxnDate), MONTH(TxnDate)) BETWEEN ' . \$iniFecha . ' AND ' . \$finFecha;``

89.8k
Accepted
``````\$iniFecha = '2018-01-01';
\$finFecha = '2018-03-31';
\$phql = 'SELECT SUM(Subtotal + SalesTaxTotal) as TotalVentas FROM invoice WHERE TxnDate >= "' . \$iniFecha . '" AND TxnDate <=
"' . \$finFecha .'"';``````

Try like this, note that I added quotes around your php variables. Otherwise sql treats them as functions and explodes ;)

That's why using QueryBuilders with bind params is better idea, you wont waste time with such silly errors :)

11.0k

Thank you Nikolay,

Just in case is there someone in the community that has been using Koolreport?