Invalid default value for 'Date'

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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());

Answer 4

You can change the column definition with:

ALTER TABLE transactionentry MODIFY COLUMN `Date` date null;

Allow null works for me.

Answer 5

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