#1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column '' at row 1

We encounter following  error when we moved our application from existing environment to new environment

#1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column ' at row 1

This error is due to our application NO_ZERO_IN_DATE mode and NO_ZERO_DATE is enabled

NO_ZERO_IN_DATE

This mode affects whether the server permits dates in which the year part is nonzero, but the month or day part is 0. (This mode affects dates such as '2010-00-01' or '2010-01-00', but not '0000-00-00'. To control whether the server permits '0000-00-00', use the NO_ZERO_DATE mode.) This also depends on whether strict SQL mode is enabled.

•      If this mode is not enabled, dates with zero parts are permitted and inserts produce no warning.
•          If this mode is enabled, dates with zero parts are inserted as '0000-00-00' and produce a warning.
•         If this mode and strict mode are enabled, dates with zero parts are not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, dates with zero parts are inserted as '0000-00-00' and produce a warning.

NO_ZERO_DATE

This 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.

Please refer details of this options in https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

Solution

Change the sql_mode to allow zero dates, by removing NO_ZERO_DATE and NO_ZERO_IN_DATE. The change can be applied in the my.cnf file, so after a restart of MySQL Server, sql_mode variable will be initialized to the setting in my.cnf.

1.       Change Via PhpMyAdmin

Login to PhpMyAdmin and go to Variables and Sql_mode. Copy the existing value remove NO_ZERO_DATE and NO_ZERO_IN_DATE and apply

2.       Via Command

• Get sql_mode : SHOW VARIABLES LIKE 'sql_mode';
•  Result may show as follows

      ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 

•   Remove on the result : NO_ZERO_IN_DATE,NO_ZERO_DATE
•  Set new configuration : SET GLOBAL sql_mode =’ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION ‘