How to Resolve MySQL Error 1292: Incorrect Datetime Value During Table Alteration?

MySQL Error 1292: Incorrect Datetime Value

In MySQL, attempting to modify a table's data type or character set can result in the "Incorrect datetime value" error. This scenario arises when existing data conflicts with the new data type or encoding.

For instance, upon importing data from a database with the latin1 character set into MySQL 5.7, which uses UTF-8, you may encounter this error when trying to modify certain columns.

Specifically, if a datetime column contains the invalid value '0000-00-00 00:00:00', you may face this issue. To resolve it, try the following steps:

1. Verify if there are any rows with the invalid datetime value:

SELECT * FROM users WHERE created = '0000-00-00 00:00:00';

2. Attempt to update the invalid values to NULL using the CAST function:

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

3. Once the invalid values are updated, you can proceed with the intended alteration:

ALTER TABLE users MODIFY first_name varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

It's important to note that the CAST function allows you to convert the datetime value to a string, which can then be compared against the invalid value and updated to NULL without raising the error.

The above is the detailed content of How to Resolve MySQL Error 1292: Incorrect Datetime Value During Table Alteration?. For more information, please follow other related articles on the PHP Chinese website!