SQL Error [1525] [HY000]: Incorrect DATETIME value: ''

Question

I get the error SQL Error [1525] [HY000]: Incorrect DATETIME value: '' when running following query:

SELECT * FROM mytable where date_time_column >=''

The query was working for the past few months and it started giving the above error since yesterday.

My MySQL version is 8.0.17.

I tried running the code via older versions of my code and still get the same error. Even trying it in MySQL Workbench gives the same error.

What Database config changes would have caused this?

Answer 1

It's seems not related to database changes itself, but something changed in version 8.0.16 regarding comparison on DATETIME and TIMESTAMP types against strings. The behavior is documented already in 8.0.16 change log as following:

When comparing DATE values with constant strings, MySQL first tries to convert the string to a DATE and then to perform the comparison. When the conversion failed, MySQL executed the comparison treating the DATE as a string, which could lead to unpredictable behavior. Now in such cases, if the conversion of the string to a DATE fails, the comparison fails with ER_WRONG_VALUE. (Bug #29025656)

Also, this is reported as a bug here.

PD: Your query works on my old 5.7 MySQL version.