Issue in date comparison in MySQL

Question

I have MySQL with below settings Server version: 5.0.77 MySQL client version: 5.0.41 Protocol version: 10 MySQL charset: UTF-8 Unicode (utf8) MySQL connection collation: utf8_unicode_ci

I am just doing a simple query and it returned wrong SELECT * FROM table1 WHERE mydate >= '2010-08-30'

Today is 8/30 and I have 1 row with mydate is '2010-06-01' and that row was selected. Why? If I do this, it returns zero record which is correct SELECT * FROM table1 WHERE mydate >= NOW()

The first query was executed correctly in local but not live server.

How do I go about troubleshooting this? Is it because of different MySQL version, time setting, server setting... What am I looking for?

Thanks.

Answer 1

This is possibly a manifestation of a bug in MySQL that was fixed in versions >= 5.0.50. I believe that the bug only exhibits when there is an index on the date column.

See this bug report for details.

This may not be the bug you are experiencing, but nevertheless it seems very likely that the root cause of your problem is a MySQL bug that has been fixed in one version but not the others. I would advise that you bring the two versions of the database in line.

Answer 2

In MYSQL you should not use single quotes '' around your date, this causes the MYSQL server to interpret your entry as a string rather than a date. I would also suggest using date() around your date entry in the database to ensure that it is indeed a date.

Here is an example of how I would perform your above query in MYSQL:

SELECT * FROM table1 WHERE date(mydate) >= 2010-08-30

This query should return your desired results.