MySQL select date range issue

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.

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)

MySQL's needs values in this format to do a proper comparison:

YYYY-MM-DD

you could use STR_TO_DATE to convert your string into the right format.

Plus obviously, the Date field needs to be of the DATE or DATETIME type.

try to format the values as DATE.. as in

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