Why am I getting \'Invalid default value for \'create_date\'\' when using `0000-00-00 00:00:00` as the default for a TIMESTAMP field in MySQL?

Fixing Invalid Default Value for TIMESTAMP Field in MySQL

When attempting to create a table with a TIMESTAMP field and specifying a default value of '0000-00-00 00:00:00', you may encounter the error "Invalid default value for 'create_date'." This is often due to a MySQL server SQL Mode setting.

Explanation

The NO_ZERO_DATE SQL Mode enforces strictness around the handling of zero dates. It prevents the insertion of '0000-00-00' as a valid date, considering it an invalid value.

Solution

To resolve the error, you can either:

• Disable the NO_ZERO_DATE SQL Mode:

  <code class="mysql">SET SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ENGINE_SUBSTITUTION';</code>

• Use the IGNORE option to allow the insertion of zero dates:

  <code class="mysql">CREATE TABLE IF NOT EXISTS `erp`.`je_menus` (
    `id` INT(11) NOT NULL AUTO_INCREMENT ,
    `name` VARCHAR(100) NOT NULL ,
    `description` VARCHAR(255) NOT NULL ,
    `live_start_date` DATETIME NULL DEFAULT NULL ,
    `live_end_date` DATETIME NULL DEFAULT NULL ,
    `notes` VARCHAR(255) NULL ,
    `create_date` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' IGNORE,
    `created_by` INT(11) NOT NULL ,
    `update_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    `updated_by` INT(11) NOT NULL ,
    `status` VARCHAR(45) NOT NULL ,
    PRIMARY KEY (`id`)
  ) ENGINE = InnoDB;</code>

Once you make these adjustments, you should be able to successfully create the table with the specified default value for the TIMESTAMP field.

The above is the detailed content of Why am I getting 'Invalid default value for 'create_date'' when using `0000-00-00 00:00:00` as the default for a TIMESTAMP field in MySQL?. For more information, please follow other related articles on the PHP Chinese website!