[SOLVED] Setting default value for DATE type column to current date without time part?

Setting default value for DATE type column to current date without time part?

NOTE: The question is about DATE type, not Datetime nor Timestamp

How to alter column of date data type to use current date by default? I saw a lot of examples for datetime (with time part), but not for date. I have tried:

ALTER TABLE `accounting` ALTER `accounting_date` 
  SET DEFAULT CURRENT_DATE;
ALTER TABLE `accounting` CHANGE `accounting_date` 
  `accounting_date` DATE NOT NULL DEFAULT CURRENT_DATE;

I also tried with CURDATE(), NOW(), CURRENT_DATE() ...

Solution

Probably you cannot set default value for 'date' data type in mysql. You need to change the type to timestamp or datetime.

You may have a look at this similar question.

Invalid default value for 'Date'

EDIT:

In version 5.6.5, it is possible to set a default value on a datetime column, and even make a column that will update when the row is updated. The type definition:

CREATE TABLE foo (
    `creation_time`     DATETIME DEFAULT CURRENT_TIMESTAMP,
    `modification_time` DATETIME ON UPDATE CURRENT_TIMESTAMP
)

Reference: http://optimize-this.blogspot.com/2012/04/datetime-default-now-finally-available.html