Incorrect datetime value Database Error Number: 1292

I discovered after upgrading to MySQL 5.7 that this error started occurring in random situations, even when I wasn’t supplying a date in the query.

This appears to be because previous versions of MySQL supported dates like 0000-00-00 00:00:00 (by default) however 5.7.4 introduced some changes to the NO_ZERO_DATE setting. If you still have old data present when using a newer MySQL version, then random errors may crop up.

I needed to perform a query like this to reset all the zero dates to another date.

# If the columns supports NULL, use that
UPDATE table SET date_column = NULL WHERE date_column < '1000-01-01';

# Otherwise supply another default date
UPDATE table SET date_column = '1970-01-01' WHERE date_column < '1000-01-01';

Alternatively, you may be able to adjust the NO_ZERO_DATE setting, although note what the docs say about it:

The NO_ZERO_DATE mode affects whether the server permits ‘0000-00-00’ as a valid date. Its effect also depends on whether strict SQL mode is enabled.

• If this mode is not enabled, ‘0000-00-00’ is permitted and inserts produce no warning.

• If this mode is enabled, ‘0000-00-00’ is permitted and inserts produce a warning.

• If this mode and strict mode are enabled, ‘0000-00-00’ is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, ‘0000-00-00’ is permitted and inserts produce a warning.

As of MySQL 5.7.4, NO_ZERO_DATE is deprecated. In MySQL 5.7.4 through 5.7.7, NO_ZERO_DATE does nothing when named explicitly. Instead, its effect is included in the effects of strict SQL mode. In MySQL 5.7.8 and later, NO_ZERO_DATE does have an effect when named explicitly and is not part of strict mode, as before MySQL 5.7.4. However, it should be used in conjunction with strict mode and is enabled by default. A warning occurs if NO_ZERO_DATE is enabled without also enabling strict mode or vice versa. For additional discussion, see SQL Mode Changes in MySQL 5.7.

Because NO_ZERO_DATE is deprecated, it will be removed in a future MySQL release as a separate mode name and its effect included in the effects of strict SQL mode.

From http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date