MySQL select date range issue

Question

I'm getting some strange results from using BETWEEN in my sql query. Wondering if anyone can help me to understand why i am getting these results.

I'm searching a date range in the format of dd/mm/yyyy. So i want to select all entries within a certain date range.

$dbSearchRecords_result = "SELECT * FROM $tbl_name WHERE Date BETWEEN '$DateFrom_order' AND '$DateTo_order'";

$dbSearchRecords_result = mysql_query($dbSearchRecords_result);

I am then calling the results in a while statement from an array

while ($row = mysql_fetch_array($dbSearchRecords_result)){

Now if I search BETWEEN 12/02/2011 14/02/2011 there is a date returned from 13/12/2010.

Yet if I search 12/02/2011 13/02/201 I do not get the result of 13/12/2010.

Any ideas would be most appreciated.

Thanks.

Answer 1

The BETWEENoperator is most likely reading your ranges as strings. From the book:

For best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to a DATE.

So, try:

SELECT * FROM `mytable` 
WHERE `date` BETWEEN CAST('2011-01-02' AS DATE) AND CAST('2011-12-02' AS DATE)