Default Date Value in MySQL create table not working

Question

I have the following create table statement (in MySQL):

CREATE TABLE bugs (id INT, open_date DATE, 
close_date DATE DEFAULT STR_TO_DATE('9999-99-99', '%Y-%m-%d'), severity INT);

Unfortunately, it is not working and returns an error because of the default date value. Any other way to set a particular default value to date when using the create table statement? Thanks in advance.

score 0 · Accepted Answer · edited May 23 '17 at 12:09

0

Try this

CREATE TABLE bugs (id INT, open_date DATE, 
close_date DATE DEFAULT '9999-99-99', severity INT);

for more information, use the below link:-

How do you set a default value for a MySQL Datetime column?

edited May 23 '17 at 12:09

Community

1
1

answered Feb 16 '17 at 06:14

Munish Chouhan

318
4
10

Not working. returns: CREATE TABLE bugs (id INT, open_date DATE, close_date DATE DEFAULT '9999-99-99', severity INT) Error Code: 1067. Invalid default value for 'close_date' – mj1261829 Feb 16 '17 at 06:24
oh sorry Please use '9999-9-9' instead of '9999-99-99' – Munish Chouhan Feb 16 '17 at 06:29
Because 99 is not valid date or month, you can use till 31 for date and 12 for month – Munish Chouhan Feb 16 '17 at 06:30

score 0 · Answer 2 · answered Feb 16 '17 at 06:35

MySQL's default DATE field format is YYYY-MM-DD. The supported range is 1000-01-01 to 9999-12-31. But you can't insert only date there,You need timestamp here

CREATE TABLE bugs
      (id INT, open_date DATE, 
       close_date TIMESTAMP DEFAULT '2016-11-30 22:30:00',
       severity INT
      );

Fiddle here

Since MySQL 5.6 DATE should work with default value NOW()

For more on Mysql dates read this

Default Date Value in MySQL create table not working

2 Answers2