Default dates in legacy systems, particularly MySQL, were set to 0000-00-00. From version 5.6 on, MySQL stopped allowing users to enter these values without first changing the SQL Mode.
Similar to this, SQL Server also rejects “0000-00-00” as a default value.
Let’s see with an example-
CREATE TABLE TestTables(
SomeID INT PRIMARY KEY,
HireDate DATETIME NOT NULL DEFAULT ‘0000-00-00 00:00:00’);
There won’t be any problems creating the table. However, it will throw an error as shown below when you attempt to insert the value into it.
INSERT INTO TestTables (SomeID) VALUES (1001)
Msg 242, Level 16, State 3, Line 5
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.
Let’s see what happens in MySQL.
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
In MySQL, the allowed date range is 1000-01-01 to 9999-12-31. And for SQL Server, the acceptable default date is 1900-01-01.
Any RDBMS product, including MySQL, SQL Server, and others, must limit the use of the invalid date value “0000-00-00.” Even if we switch the data type from DATE to STRING/VARCHAR to hold zero dates, the attempt to convert them back to dates would fail.
Hope you find this article helpful.
Big Data and SQL 