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

user_13219197 0
Viewed 7241

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?

mysql
2 Answers

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.

user_117258160

For me the issue was that a TRIGGER would try to update that same field after every update to a row.

The trigger would not use the correct format when making a comparison.

user_17323590
Related