Define default date value in MySQL, similar to timestamp

Question

I'm using MySQL (nobody's perfect), version 4.1 and I'm used to define some timestamp columns like that:

ALTER TABLE foo ADD creation TIMESTAMP DEFAULT NOW() ;

I'd like to do exactly the same thing, but for a DATE field. The reason being I don't need a TIMESTAMP precision and since no functional index exists in MySQL, I cannot access quickly to the rows with a given date (regardless of time of day). So I tried the following but it just does not work:

ALTER TABLE foo ADD creation_date DATE DEFAULT CURDATE() ;
ERROR 1067 (42000): Invalid default value for 'creation_date'

Or even

ALTER TABLE foo ADD creation_date DATE DEFAULT DATE(NOW()) ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(now())' at line 1

Which does not work either.

Any Ideas?

Answer 1

In MySQL default values have to be constant. Functions or expressions are not allowed.

The exception ist the TIMESTAMP type, for which CURRENT_TIMESTAMP is a valid non constant default value.

See 4.1 manual: Data Type Default Values