We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

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 ;
1


23.9k

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;


93.7k
Accepted
answer 
$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 :)



23.9k

Thank you Nikolay,

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

I will like to try Koolreport, they already had packages for Laravel and Codelgniter, any ideas to start with?



93.7k
edited Feb '19

Better make new topic about this question so more people see it :)

I have not used it, sorry. But loooking in the docs i dont think you need specific Phalcon package, you can simply use it inside your code, just follow the steps here :)

https://www.koolreport.com/docs/quick_start/