Implicit cast from now() to a date field

Question

Hello,

I have an issue with MySQL 5.1. A datetime data type isn't implicitly casted to match a date column.

 SELECT * FROM my_table WHERE my_date_field = NOW()

This request doesn't return any rows using MySQL 5.1, but works well with version 5.0. If we use CURDATE() instead of NOW() it works both in MySQL 5.0 and MySQL 5.1. If the cast is explicit (CAST(NOW() AS DATE)), it also works both in MySQL 5.0 and MySQL 5.1.

The problem only appears with implicit cast from datetime to date. Doesn't anyone already encountered this issue or has a clue about how to solve this problem? I know it's not the best to use NOW() instead of CURTIME(), but this isn't the question here. It is currently used in an application and the purpose is to avoid rewriting everything.

Thanks!

Answer 1

The behaviour makes sense because NOW() is of the type DATETIME, and CURDATE() of the type DATE.

As for why the variables are cast in one server version, and not in the other - this sounds more like a difference in server modes, i.e. the one instance where the cast fails being more strict than the other.

An interesting point from that document (not sure whether this is your problem but it could be): ALLOW_INVALID_DATES:

This mode is implemented in MySQL 5.0.2. Before 5.0.2, this was the default MySQL date-handling mode. As of 5.0.2, the server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error. To allow such dates, enable ALLOW_INVALID_DATES.

Anyway, I'm not sure whether it makes sense digging through changelogs trying to find out what changed when. I would tend to make the behaviour work in both situations (i.e., if I understand you correctly, use CURDATE()) and be done with it.

Answer 2

only compare the date part of your datetime column:

SELECT * FROM my_table WHERE DATE(my_date_field) = DATE(NOW())

Answer 3

This was fixed in MySQL 5.1.17 to allow CURDATE() to evaluate to less than NOW() when stored in a DATE column.

Now, when comparing a DATE to a DATETIME, they are compared as DATETIME. When a DATE is cast to a DATETIME, it has a zero hour.

If my_date_field is '2010-01-01' AND NOW() is '2010-01-01 05:01:01', when they are compared, my_date_field is promoted to '2010-01-01 00:00:00', which is obviously less than '2010-01-01 05:01:01'.

Originally, when the left side was a column, the promotion from DATE to DATETIME didn't occur. However, apparently they thought it was more consistent to always promote it.

Sorry, but you just got lucky that it worked before. A date that has a zero hour should evaluate to less than the same date with a non-zero hour.

Unfortunately, there is no way to turn off this "bug fix". Your only solution is to change NOW() to CURDATE() or roll back to a prior version.

Actually, you could compile your own version and either undo the "bug fix" or override the NOW() function.