Behoston Behoston - 7 months ago 44
MySQL Question

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)

Answer

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.