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.

Are you sure your date variables are actually formatted as proper dates? — willdanceforfun
– willdanceforfun, Commented Feb 4, 2011 at 10:54
Have you checked that the generated SQL statement from PHP is what you expected? — macarthy
– macarthy, Commented Feb 4, 2011 at 10:55
@ KeenLearner. I've only just realized that which is pretty silly of me. I'm pretty sure that is the main issue. Thanks mate. — Samuel Meddows
– Samuel Meddows, Commented Feb 4, 2011 at 10:59

rxmnnxfpvg · Accepted Answer · 2011-02-04 10:56:01Z

7

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)

answered Feb 4, 2011 at 10:56

rxmnnxfpvg

31.2k50 gold badges129 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pekka · Accepted Answer · 2011-02-04 10:52:39Z

5

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.

answered Feb 4, 2011 at 10:52

Pekka

451k150 gold badges989 silver badges1.1k bronze badges

Comments

eumiro · Accepted Answer · 2011-02-04 11:24:14Z

1

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

edited Feb 4, 2011 at 11:24

eumiro

214k36 gold badges307 silver badges264 bronze badges

answered Feb 4, 2011 at 10:54

ThomasM

2,6773 gold badges26 silver badges30 bronze badges

Collectives™ on Stack Overflow

MySQL select date range issue

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related