MySQL Date Query issue

Question

I have questions regarding MySQL date and querying with it.

First:

SELECT * FROM post WHERE DATE(Post_Date)="2009-03-25"

returns 0 results

SELECT * FROM post WHERE Post_Date="2009-03-25"

returns 71 results

SELECT * FROM post WHERE Post_Date>="2009-03-25"

returns 379 results

I understand that the second query returning 71 results match only posts with 2009-03-25 00:00:00 as the Post_Date and the third query shows everything. BUT why does the first query SHOW 0 RESULTS?? Please help! I checked the MySQL cnf and the date_format is set to %Y-%m-%d

Second:

SELECT * FROM post WHERE DATE(Post_Date)="2009-03-25"

RETURNS results on WINDOWS!

SELECT * FROM post WHERE DATE(Post_Date)="2009-03-25"

NO RESULTS in Linux!

Any pointers will be helpful! Is there a configuration file that I need to change to make this work in Linux?

Answer 1

Diagnostic step: run the query SELECT DATE('2009-03-25 08:30:00') on each system. The result will probably tell you what's going on. (Likely a version issue.)

Answer 2

Not sure what to day about your first part, but as for the second: Have you check to make sure that both your servers on windows and Linux have the same data in their respective databases? If you are sure that they are, you may want to check if the Linux database give any results for that year or year-month rather than only the specific year-month-date.