How to Fix MySQL Error 1292: \'Incorrect datetime value\'?

Incorrect Datetime Value: Resolving MySQL Error 1292

When converting an existing database with datetime values stored as '0000-00-00 00:00:00', users may encounter MySQL error 1292: "Incorrect datetime value." This error occurs due to version conflicts between the original MySQL database (e.g., 5.1) and newer versions (e.g., 5.7), which handle datetime values differently.

To resolve this issue, the following steps can be taken:

Option 1: Convert '0000-00-00 00:00:00' to NULL

1. Attempt to convert datetime columns with '0000-00-00 00:00:00' to NULL using the following query:

ALTER TABLE users MODIFY created datetime NULL DEFAULT '1970-01-01 00:00:00';

If this fails, proceed to Option 2.

Option 2: Use CHAR Conversion

1. Update the affected rows by setting the datetime column to NULL only if its value is '0000-00-00 00:00:00' as a character string:

UPDATE users SET created = NULL WHERE CAST(created AS CHAR(20)) = '0000-00-00 00:00:00';

This alternative query uses the CAST function to convert datetime values to a character string of 20 characters and only updates rows where the result matches '0000-00-00 00:00:00'.

Once the updates are complete, you should be able to successfully modify the database table's character set and collation without encountering the Incorrect datetime value error.

The above is the detailed content of How to Fix MySQL Error 1292: \'Incorrect datetime value\'?. For more information, please follow other related articles on the PHP Chinese website!