Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL DATE field with default CURDATE(). NOT DATETIME

It is possible to set default value on DATE (NOT DATETIME) column in MySQL 5.7 to current date?
I try this (generated by Workbench):

ALTER TABLE `db`.`table` CHANGE COLUMN `column` `column` DATE NOT NULL DEFAULT CURDATE() ;

but not works for me. (no data in table)

like image 458
Behoston Avatar asked Oct 01 '16 16:10

Behoston


1 Answers

No, you cannot. The documentation is pretty clear on this:

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 TIMESTAMP and DATETIME columns. See Section 12.3.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

You can do one of the following:

  • Set up a column with a default value for the DATETIME. Create view that extracts the date as a separate column.
  • Create an insert trigger to set the date column.
like image 197
Gordon Linoff Avatar answered Nov 04 '22 13:11

Gordon Linoff