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
mysql select between two dates
Moderator: General Moderators
-
sectionLeader123
- Forum Commoner
- Posts: 31
- Joined: Fri Oct 11, 2013 8:46 am
Re: mysql select between two dates
You can't use BETWEEN when you format the date as d/m/Y. Leave it as Y-m-d:
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.
Code: Select all
...WHERE Jobs.J_DateRec BETWEEN '2014-01-13' AND '2014-01-14'