Error code 1067, SQL state 42000: Invalid default value for 'memberSince' when compiling a table

Question

I'm adding a new table to the list of existing tables:

CREATE TABLE Counselor (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    firstName VARCHAR (50),
    nickName VARCHAR (50),
    lastName VARCHAR (50),
    telephone VARCHAR (25),
    email VARCHAR (50),
    memberSince DATE DEFAULT '0000-00-00',
    PRIMARY KEY (id)
);

Execution is interrupted with the following message:

Error code 1067, SQL state 42000: Invalid default value for 'memberSince'

What can I do to fix it?

Answer 1

In MySQL, '0000-00-00' is not a valid date. According to the MySQL docs:

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

I recommend using NULL if you don't have a meaningful default value you can use.

CREATE TABLE Counselor (
...
memberSince DATE DEFAULT NULL,
...