Create table fail in mysql when using CURDATE() as default

Question

I'm trying to create the following table using phpmyadmin sql console:

CREATE TABLE dates
(
id int NOT NULL,
id_date datetime NOT NULL DEFAULT CURDATE(),
PRIMARY KEY (id)
)

However I get the following error:

It shows "CURDATE()" in red, so I guess that's the problem.

Could anyone help me out here ?

Solution

You can't use CURDATE() as a default value.

Instead you can use a TIMESTAMP column with DEFAULT CURRENT_TIMESTAMP. Then you will have to ignore the time part of it.

Example SQL code:

CREATE TABLE dates
(
    id int NOT NULL,
    id_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);
INSERT INTO dates (id) VALUES (1);
SELECT id, DATE(id_date) AS id_date FROM dates;

Result:

id  id_date
1   2010-09-12