Invalid default value for ‘Date’

I want to set date as default value for date in mysql (not timestamp), but the following error appear

ALTER TABLE `RMS`.`transactionentry` 
CHANGE `Date` `Date` DATE DEFAULT NOW() NOT NULL

Error

Invalid default value for 'Date'

Same Case

alter table `RMS`.`transactionentry` 
change `Date` `Date` date default 'CURRENT_DATE' NOT NULL

5 Answers

alter table `RMS`.`transactionentry`
change `Date` `Date` date default current_timestamp NOT NULL

Updated:

I don’t think you can achieve that with mysql date. You have to use timestamp or try this approach..

CREATE TRIGGER transactionentry_OnInsert BEFORE INSERT ON `RMS`.`transactionentry`
    FOR EACH ROW SET NEW.dateColumn = IFNULL(NEW.dateColumn, NOW());

The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.

For Detail: Refer dev.mysql.com

remember it could be an issue with MYSQL version itself 5.6 allows timestamp with out a default value and doesn’t requires to set them as null however 5.7 requires you to explicitly set the default if not then nul

According to the MySQL site:

The DEFAULT value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column.

So:

alter table `RMS`.`transactionentry` 

change `Date` `Date` date default CURRENT_TIMESTAMP NOT NULL

should work. Your issue was you’re making it a string by placing quotes around it.

I copied this the above statement from MySQL IDE, But according to your suggestion, I remove the quotes and its same error,means not working. 

– 

ERROR 1067 (42000): Invalid default value for 'Date' . columne type is DATE, not TIMESTAMP, hence such default value will not work. 

– 

You can change the column definition with:

ALTER TABLE transactionentry MODIFY COLUMN `Date` date null;

Allow null works for me.

OP doesn’t want to set the column to NULL, he wants to have the current date as a default value. 

– 

Nick, Although true, for many other people with similar situation this is a viable solution 

– 

source : https://stackoverflow.com/questions/11573586/invalid-default-value-for-date