MySQL Error 1067 – Invalid default value for Date

You may have noticed that date columns’ default dates in legacy systems are 0000-00-00. The data cannot be changed, therefore you’ll have to accept it. But in the current versions, particularly on AWS RDS, you’ll get the following problem when you attempt to create a table with the same default value (i.e. 0000-00-00).

> 1067 – Invalid default value for ‘DateColumn’
> Time: 0.001s

Prior to version 5.6, MySQL accepted the default date of 0000-00-00, but now it accepts dates in the range of 1000-01-01 to 9999-12-31. Although MySQL allows either strings or numbers to be assigned as values to DATE columns, it displays date data in the ‘YYYY-MM-DD’ format.

In a similar vein, the permitted range for date-time data type is “1000-01-01 00:00:00.000000” to “9999-12-31 23:59:59.999999”.

The time zone range for a timestamp is from “1970-01-01 00:00:01.000000” to “2038-01-19 03:14:07.999999” UTC.

Let’s reproduce the error:

DROP TABLE IF EXISTS tbEmp;

CREATE TABLE `tbEmp` (
`employeeId` int NOT NULL DEFAULT ‘0’,
`firstname` varchar(100) DEFAULT NULL,
`middleName` varchar(100) DEFAULT NULL,
`lastName` varchar(100) DEFAULT NULL,
`businessPhone` varchar(100) DEFAULT NULL,
`businessEmail` varchar(228) DEFAULT NULL,
`JoinDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`EOSDate` datetime NOT NULL DEFAULT ‘0000-00-00’
) ENGINE=InnoDB;

> 1067 – Invalid default value for ‘EOSDate’
> Time: 0.001s

Solution:
The SQL mode that enforces the guidelines for transactional tables is the root of the problem. The server’s ability to accept the date ‘0000-00-00’ depends on the strict mode setting. The character ‘0000-00-00’ is allowed and inserts do not trigger a warning if strict mode is not activated. If strict mode is set, the characters “0000-00-00” are not allowed, and insertion fail unless IGNORE is also specified. ‘0000-00-00’ is allowed for the INSERT IGNORE and UPDATE IGNORE commands, and inserts result in a warning.

DROP TABLE IF EXISTS tbEmp;

SET sql_mode = ”;

CREATE TABLE `tbEmp` (
`employeeId` int NOT NULL DEFAULT ‘0’,
`firstname` varchar(100) DEFAULT NULL,
`middleName` varchar(100) DEFAULT NULL,
`lastName` varchar(100) DEFAULT NULL,
`businessPhone` varchar(100) DEFAULT NULL,
`businessEmail` varchar(228) DEFAULT NULL,
`JoinDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`EOSDate` datetime NOT NULL DEFAULT ‘0000-00-00’
) ENGINE=InnoDB;

This will succeed.