MySQL selects a date range

Question

Hey guys. I am getting some weird results from using BETWEEN in my sql query, and I am wondering if anyone can help me figure out why I am getting the results I get.

I'm looking for a date range in dd / mm / yyyy format. Therefore, I want to select all entries within a specific date range.

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

Then I call the results in the while statement from the array

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

Now, if I search for BETWEEN 12/02/2011 02/14/2011, the date is back from 12/13/2010.

But if I search for 12/02/2011 02/13/2012, I do not get the result on 12/13/2010.

Any ideas would be most appreciated.

Greetings.

Answer 1

The BETWEEN statement most likely reads your ranges as strings. From book:

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

So try:

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

Answer 2

MySQL needs values ​​in this format for proper comparison:

 YYYY-MM-DD 

you can use STR_TO_DATE to convert the string to the desired format.

Plus, obviously, the Date field must be of type Date or DATETIME .

Answer 3

try formatting the values ​​as DATE .. as in

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