Search code examples
mysqlsqlrdbms

Syntax error when using Curdate()

Am trying to create a table containing a column which tracks the time when a row has been inserted.

The below query works fine. Creation of table is done and am able to view the date and time in the column track_date when data is added. 

CREATE TABLE tracker(id int, track_date DATETIME DEFAULT NOW());

Suppose I now need to track just the date so I now run below query but I get syntax error.

CREATE TABLE tracker_2(id int, track_date DATE DEFAULT CURDATE());

Can you please advise.

Solution

  • The MySQL documentation is quite clear on this point. You can set the default for a DATETIME or TIMESTAMP column, but not for a date:

    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.

    Your options are to use a trigger or to remove the time component when you query the column.

    (I should note that the statement is a little misleading, because NOW() can be used as the default for a DATETIME as both the question and the documentation to clarify. The paragraph is referring to DATE columns, in particular.)