Incorrect date value '0000-00-00' for DATE or DATETIME - 快乐的在一起

When setting a default value for a column of DATE or DATETIME datatype in MySQL, if you use ‘0000-00-00 00:00:00’ as the default value, you might run into this error:

ERROR 1067 (42000): Invalid default value for 'column_name'

Or when inserting value:

ERROR 1292 - Incorrect date value: '0000-00-00'

The reason is, since version 5.7, MySQL enables SQL Strict Mode by default and does not permit ‘0000-00-00’ as a valid date value.

Steps to fix this:

1. Check the MySQL mode

You can see the SQL mode from the MySQL configuration file or run this command:

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

2. Disable Strict Mode

If strict mode is enabled, either or both STRICT_TRANS_TABLES or STRICT_ALL_TABLES are enabled. So you can disable it by commenting it out from the MySQL configuration file, or running the command:

SET sql_mode = '';

SET GLOBAL sql_mode = '';

3. Restart MySQL

Then restart MySQL and you should be able to use ‘0000-00-00’ as a value for DATE or DATETIME column.

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.