[SOLVED] Specifying date range in query using DATE_FORMAT.

[Mark1inLA]

I understand that if you want to query from a daterange, this will work:

 

select * from mytable 
where 
datecol >= '2009-01-01' AND 
datecol <= '2009-09-22'

 

But when i use this query, it ignores the year and returns records that match the month and day:

select * from mytable 
where 
DATE_FORMAT(datecol, '%m-%d-%Y') >= '01-01-2009' AND
DATE_FORMAT(datecol, '%m-%d-%Y') <= '09-22-2009'

 

Anyone know the reason and can explain what's going on? Instead of dwelling too deep why, I ended up rewriting the query to use BETWEEN operator, but i just found this odd.

 

[PFMaBiSmAd]

Only dates in the format YYYY-MM-DD, where the fields that make it up are ordered left to right, year (most significant digit) to day (least significant digit) can be compared using greater-than/less-than operators. This is why the DATE data type is formatted as YYYY-MM-DD. So that it can be sorted and compared correctly.

[artacus]

Don't do that. You're forcing your db to go through every row converting the date to a string in order to compare with a string.

 

Instead, convert your strings to dates. Then you only only have to do the conversion twice and better yet, the db can use indexes.

[Mark1inLA]

Thanks for the explanation and heads-up. i thought the date_format function did a behind-the-scene conversion so that the query will be optimized. I guess that's not the case..

[PFMaBiSmAd]

Something tells me that you are actually asking about a query like the following -

 

select * from mytable
where
datecol >= STR_TO_DATE('01-01-2009', '%m-%d-%Y') AND
datecol <= STR_TO_DATE('09-22-2009', '%m-%d-%Y')

 

which would actually be an efficient query that would work.