Page 1 of 1

mysql select between two dates

Posted: Tue Jan 14, 2014 10:03 am
by sectionLeader123
I am executing the following query which selects the J_RefNum and the J_DateRec from the Jobs table where the date received(J_DateRec) is between $dateFrom and $dateTo.
In this case I have been executing the query using the following dates 13/01/2014 and 14/01/2014.
So $dateFrom = '13/01/2014' and $dateTo = '14/01/2014'

$query = "SELECT J_RefNum, DATE_FORMAT(Jobs.J_DateRec, '%d/%m/%Y') AS J_DateRec FROM Jobs WHERE DATE_FORMAT(Jobs.J_DateRec, '%d/%m/%Y') BETWEEN '$dateFrom' AND '$dateTo'";

The query does return the correct results but it also returns results which are not in between the two dates specified above.
The incorrect dates which it returns are 13/11/2013 and 13/12/2013 which are not in between the two dates 13/01/2014 and 14/01/2014.
Is there anything I am missing in my query.

Any help would be greatly appreciated
Many Thanks

Re: mysql select between two dates

Posted: Tue Jan 14, 2014 1:34 pm
by requinix
You can't use BETWEEN when you format the date as d/m/Y. Leave it as Y-m-d:

Code: Select all

...WHERE Jobs.J_DateRec BETWEEN '2014-01-13' AND '2014-01-14'
The problem is that MySQL will compare the values as the strings that you're telling it to use, and the string value "13/11/2013" sorts between "13/01/2014" (0<1) and "14/01/2014" (3<4). When you format the date as Y-m-d it will still sort as strings but it will sort correctly.