user3733831 user3733831 - 2 months ago 21x
MySQL Question

mysql DATE datatype's default value not working

I do have a column named

in my mysql table.

This is how I create it.

leave_date DATE NOT NULL DEFAULT '0000-00-00'

It was ok in my earlier mysql versions, but now I have 5.7 and when I try to create above table, I can get an error.

Message look like this:

ERROR 1067 (42000): Invalid default value for 'leave_date'

Can anybody tell me what would be the problem?

Thank you.


You problem is that you are not using a valid date.

The minimum valid date in MySQL is '1000-01-01' , each RDBMS usually have its own minimum date value. Oracle is 0001-01-01 and SQL-Server is 01/01/1753 .

EDIT: By the error you are receiving , I believe you also have time value in this column? Try changing it to datetime

ALTER TABLE employees MODIFY leave_date DATETIME NOT NULL DEFAULT '1000-10-01'

Or update the table and trunc the time value yourself, and then use your alter command.