mysql - How can I set the default value of a field as '0000-00-00 00:00:00'?

Question

How can I set the default value of a field as '0000-00-00 00:00:00'? If we can't use '0000-00-00 00:00:00' as the default? What is the basic valid tiemdate?

For instance, this is the SQL for creating my article table,

-- -----------------------------------------------------
-- Table `article`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `article` ;

CREATE TABLE IF NOT EXISTS `article` (
  `article_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `url` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `date_from` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Set the article as new or featured from a datetime.',
  `date_to` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Set the article as new or featured to a datetime.',
  `backdated_on` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'The manual datetime that is modified or input by the user.',
  `created_on` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'The permanent datetime when the article is created.',
  `updated_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'The datetime when the article is updated on.',
  PRIMARY KEY (`article_id`, `parent_id`, `template_id`),
  UNIQUE INDEX `url_UNIQUE` (`url` ASC))
ENGINE = MyISAM
AUTO_INCREMENT = 66
COMMENT = 'Entity that holds the article with one-to-one properties.';

I get this error when I run this query,

#1067 - Invalid default value for 'date_from' 

Answer 1

Cause of the error: the SQL mode

You can set the default value of a DATE, DATETIME or TIMESTAMP field to the special "zero" value of '0000-00-00' as dummy date if the sql mode permits it. For MySQL versions lower than 5.7.4 this is ruled by the NO_ZERO_DATE mode, see this excerpt of the documentation:

MySQL permits you to store a “zero” value of '0000-00-00' as a “dummy date.” This is in some cases more convenient than using NULL values, and uses less data and index space. To disallow '0000-00-00', enable the NO_ZERO_DATE SQL mode.

Additionally strict mode has to be enabled for disallowing "zero" values:

If this mode and strict mode are enabled, '0000-00-00' is not permitted
and inserts produce an error, unless IGNORE is given as well.

As of MySQL 5.7.4 this depends only on the strict mode:

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.

Check version and SQL mode

So you should to check your MySQL version and the SQL mode of your MySQL server with

SELECT version();
SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session

Enable the INSERT

You can set the sql_mode for your session with SET sql_mode = '<desired mode>'

SET sql_mode = 'STRICT_TRANS_TABLES';   

Valid range for DATETIME

The supported range for DATETIME is

[1000-01-01 00:00:00] to ['9999-12-31 23:59:59'], 

so the minimal valid DATETIME value is '1000-01-01 00:00:00'.
I wouldn't recommend to use this value though.

Additional Note

Since MySQL 5.6.5 all TIMESTAMP and DATETIME columns can have the magic behavior (initializing and/or updating), not only TIMESTAMP and only one column at most, see Automatic Initialization and Updating for TIMESTAMP and DATETIME:

As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically initializated and updated to the current date and time (that is, the current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and for at most one TIMESTAMP column per table. The following notes first describe automatic initialization and updating for MySQL 5.6.5 and up, then the differences for versions preceding 5.6.5.

You could change your CREATE TABLE statement in the case of MySQL 5.6.5 or newer to:

CREATE TABLE IF NOT EXISTS `article` (
  `article_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `url` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `date_from` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Set the article as new or featured from a datetime.',
  `date_to` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Set the article as new or featured to a datetime.',
  `backdated_on` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'The manual datetime that is modified or input by the user.',
  `created_on` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'The permanent datetime when the article is created.',
  `updated_on` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'The datetime when the article is updated on.',
  PRIMARY KEY (`article_id`, `parent_id`, `template_id`),
  UNIQUE INDEX `url_UNIQUE` (`url` ASC))
ENGINE = MyISAM
AUTO_INCREMENT = 66
COMMENT = 'Entity that holds the article with one-to-one properties.';