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

manurajhada · Accepted Answer · 2012-07-20T06:10:22.053

14

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

edited Jul 20 '12 at 06:10

answered Jul 20 '12 at 05:56

manurajhada

5,284
3
24
43

score 9 · Answer 2 · answered Jul 20 '12 at 05:56

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

score 1 · Answer 3 · answered Jan 23 '18 at 21:20

1

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

answered Jan 23 '18 at 21:20

Pravin Bansal

4,315
1
28
19

Xesued · Answer 4 · 2012-07-20T06:02:07.697

0

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.

edited Jul 20 '12 at 06:02

answered Jul 20 '12 at 05:55

Xesued

4,147
2
25
18

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. – Shahid Ghafoor Jul 20 '12 at 06:04
`ERROR 1067 (42000): Invalid default value for 'Date'` . columne type is `DATE`, not `TIMESTAMP`, hence such default value will not work. – poncha Jul 20 '12 at 06:05

score 0 · Answer 5 · edited Dec 27 '18 at 04:11

0

You can change the column definition with:

ALTER TABLE transactionentry MODIFY COLUMN `Date` date null;

Allow null works for me.

edited Dec 27 '18 at 04:11

Nick

138,499
22
57
95

answered Dec 27 '18 at 03:53

Pablo Siciliano

21
1

2

OP doesn't want to set the column to NULL, he wants to have the current date as a default value. – Nick Dec 27 '18 at 04:12
Nick, Although true, for many other people with similar situation this is a viable solution – Elia Weiss Jul 02 '20 at 12:14

score 0 · Answer 6 · answered Mar 31 '23 at 07:46

0

change `Date` `Date` datetime default 'CURRENT_DATE' NOT NULL

Use DATETIME data type for the date column instead of DATE. This will include the timestamp information.

answered Mar 31 '23 at 07:46

Nmk

1,281
2
14
25

Invalid default value for 'Date'

6 Answers6

Updated:

Linked