mysql: ERROR 1292 (22007): Incorrect date value: '0000-00-00'

MakeTopSite · 04-10-2024, 03:24 AM

Code:

mysql> UPDATE some_table SET some_date_column='2024-04-10' WHERE some_date_column='0000-00-00' ;
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'some_date_column' at row 1

Server version: 8.0.26 MySQL Community Server - GPL

What is please optimal solution ?

pan64 · 04-10-2024, 04:41 AM

without details hard to say anything. What is that database, what is that some_table and what is that some_date_column? Anyway, 0000-00-00 is not a valid date. It also may depend on the configuration of the database/table/whatever.
So how do you think we could tell what the optimal solution would be? Probably: some_date_column='1732-11-23'

NevemTeve · 04-10-2024, 05:42 AM

Code:

UPDATE some_table SET some_date_column='2024-04-10'
WHERE some_date_column<='0001-01-01';

Or maybe you are referring to NULL-value?

Code:

UPDATE some_table SET some_date_column='2024-04-10'
WHERE some_date_column IS NULL;

Guttorm · 04-10-2024, 05:48 AM

Hi

It has to do with sql mode:

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

The default has changed in newer versions. After 8.0, the default setting for allow invalid dates changed:

https://dev.mysql.com/doc/refman/8.3...sql-modes.html

The setting you need to change is probably NO_ZERO_DATE.

sundialsvcs · 04-23-2024, 07:37 AM

... but you should also fix that database! Maybe you temporarily set the configuration to allow "0000-00-00" to be referenced, having determined that the value is actually there. Then, one time, execute a query to UPDATE those values to NULL as they should be. Then, promptly set the configuration back.

(Of course, you will also need to first review the source-code of any applications which now reference that database, so that your change does not break them.)