user3733831 user3733831 - 3 months ago 38
MySQL Question

mysql DATE datatype's default value not working

I do have a column named

leave_date
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.

Answer

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.