1

I need a DATE column to default to current not timestamp

I need it to be something like 2014-11-27

CHANGE COLUMN `date` `date` DATE NULL DEFAULT TODAY()

mysql is not accepting the above because TODAY() is unknown. DATE(), DATE, CURDATE() and CURDATE also do not work

124697
  • 22,097
  • 68
  • 188
  • 315

2 Answers2

1

You need to use a TRIGGER in this case, the DEFAULT clause can only deal with constant values:

DELIMITER //
CREATE TRIGGER today BEFORE INSERT ON table
FOR EACH ROW
BEGIN
  IF NEW.`date` IS NULL THEN
    SET NEW.`date` = NOW(); 
  END IF;
END;
//
DELIMITER ;
Hartmut Holzgraefe
  • 2,585
  • 12
  • 14
  • upvoting just because the other `n` questions on SO about this has plenty of answers with timestamp (that nobody asked), and wrong answers, and this as for now seems the only useful option (even if answered plenty of times already elsewhere). – DRC Nov 27 '14 at 14:05
  • Check this answer: https://stackoverflow.com/questions/20461030/current-date-curdate-not-working-as-default-date-value/62148285#62148285 – t1f Oct 28 '21 at 12:34
1

Remark
This answer, written in 2014, applies to MySQL 5 and older.
For MySQL 8 and newer, check this answer.

The DEFAULT value must be a constant, it cannot be a function or an expression. This is stated in the documentation.

There is a single exception, when the type of the column is TIMESTAMP. Change the type of your column to TIMESTAMP if it suits your design (also read how it is initialized and updated) or use the TRIGGER solution.

axiac
  • 68,258
  • 9
  • 99
  • 134
  • Check this answer: https://stackoverflow.com/questions/20461030/current-date-curdate-not-working-as-default-date-value/62148285#62148285 – t1f Oct 28 '21 at 12:34