everyone
I wanna add a column start_date whose default value is current date(), so I use following commands:
alter table validation add column start_date date default CURDATE()
but it didn't work and told me it has syntax error.
While using such following command works well:
alter table validation add column start_date date default 0
Any suggestion is appreciated.
The CURDATE() function returns the current date. Note: The date is returned as "YYYY-MM-DD" (string) or as YYYYMMDD (numeric). Note: This function equals the CURRENT_DATE() function.
The CURDATE function returns a date based on a reading of the time-of-day clock when the SQL statement is executed at the current server. The value returned by the CURDATE function is the same as the value returned by the CURRENT DATE special register.
CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE() .
The NOW() function gives current datetime as a timestamp while CURDATE() gives only current date, not time.
From the online documentation (my bold):
The
DEFAULT
value clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression.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 a TIMESTAMP column.
That's why default 0
works (a constant) but default curdate()
doesn't (a function).
You have (at least) a couple of possibilities to work around this.
The first is to use a timestamp column instead of a date column, and set its default to CURRENT_TIMESTAMP
. Unfortunately, that means you'll have to turn that back into a date when extracting it.
The second is to use triggers to set the column value, rather than from a default value. For example, use an insert trigger to set the relevant column to the current date.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With