DATETIME Default value 0 MYSQL 5.5.25

Question

I'm trying to create these table:

CREATE  TABLE IF NOT EXISTS `qa_discountcoupons` (
  `discount_code` INT NOT NULL AUTO_INCREMENT ,
  `status_code` INT NOT NULL ,
  `discount_date` DATETIME NOT NULL DEFAULT 0 ,
  PRIMARY KEY (`discount_code`) ,
  INDEX `discounts_to_status` (`status_code` ASC) ,
  CONSTRAINT `discounts_to_status`
    FOREIGN KEY (`status_code` )
    REFERENCES `qa_status` (`status_code` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

But i get this error:

Error Code: 1067. Invalid default value for 'discount_date'

See similar http://stackoverflow.com/questions/168736/how-do-you-set-a-default-value-for-a-mysql-datetime-column — PriestVallon, Jun 12 '12 at 21:14

score 3 · Accepted Answer · edited May 23 '17 at 12:32

3

You can use:

CREATE  TABLE IF NOT EXISTS `qa_discountcoupons` (
 `discount_code` INT NOT NULL AUTO_INCREMENT ,
 `status_code` INT NOT NULL ,
 `discount_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ,
 PRIMARY KEY (`discount_code`) ,
 INDEX `discounts_to_status` (`status_code` ASC) ,
 CONSTRAINT `discounts_to_status`
 FOREIGN KEY (`status_code` )
 REFERENCES `qa_status` (`status_code` )
 ON DELETE NO ACTION
 ON UPDATE NO ACTION)
ENGINE = InnoDB;

from MySQL 5.6.5 and up.

I suggest also checking out the thread on How do you set a default value for a MySQL Datetime column? - which has a lot of comments on this.

edited May 23 '17 at 12:32

Community

1
1

answered Jun 12 '12 at 21:22

Kerbocat

378
1
9

1

there any way to set it to 0? – John Nuñez Jun 12 '12 at 21:30
4

Well - 0 is not really a date - but you could choose some arbitary date and use that as your "null point" - like January 1st 1970 or something. A good design would use NULL for 0 though. – Kerbocat Jun 13 '12 at 08:31
3

If your SQL Mode does not inclue `NO_ZERO_DATE` (http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_no_zero_date), you *could* do `discount_date DATETIME NOT NULL DEFAULT '0000-00-00'` but as advised by Kerbocat, `NULL` is definitely preferable. – RandomSeed Jun 13 '12 at 20:34

DATETIME Default value 0 MYSQL 5.5.25

1 Answers1