CODEWITHSUNDEEP
mysql
Samuel Meddows
Samuel Meddows

Reputation: 36712

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.

Upvotes: 4

Views: 8036

Answers (3)

ThomasM
ThomasM

Reputation: 2677

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')";

Upvotes: 1

rxmnnxfpvg
rxmnnxfpvg

Reputation: 30993

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)

Upvotes: 7

Pekka
Pekka

Reputation: 449525

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.

Upvotes: 5

Related Questions