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 = '';

Or

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.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS

TablePlus in Dark mode