Blog

Fix - Incorrect date value: '0000-00-00' in MYSQL
Feb 09

Fix - Incorrect date value: '0000-00-00' in MYSQL

The error is because of the SQL mode which can be strict mode as per the latest MYSQL 5.7 documentation.

Strict mode affects whether the server permits '0000-00-00' as a valid date: If strict mode is not enabled, '0000-00-00' is permitted and inserts produce no warning. If strict mode is 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.

To Check MYSQL mode

SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session

Disabling STRICT_TRANS_TABLES mode

However, to allow the format 0000-00-00 00:00:00 you have to disable STRICT_TRANS_TABLES mode in MySQL config file or by Query

By query

SET sql_mode = '';

or

SET GLOBAL sql_mode = '';

Using the keyword GLOBAL requires super privileges and it affects the operations all clients connect from that time on

MySQL sql_mode - Get and Set sql_mode (Strict Mode) Settings

MySQL sql_mode option defines supported SQL syntax, and data validation performed by MySQL.

Syntax SET [GLOBAL|SESSION] sql_mode='mode1,mode2, …'
Quick Example SET sql_mode = 'ANSI_QUOTES,PIPES_AS_CONCAT';
Strict Mode When STRICT_TRANS_TABLES or STRICT_ALL_TABLES is specified
MySQL Configuration You can set sql_mode in my.cnf (Unix), my.ini (Windows), or --sql-mode (command line)