Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

CURDATE() causes an syntax error

Tags:

database

mysql

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.

like image 738
Mark Ma Avatar asked Oct 18 '11 05:10

Mark Ma


People also ask

What is the purpose of Curdate () function?

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.

What is SQL Curdate?

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.

Which of these is a synonym for the Curdate () function?

CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE() .

What is the difference between Curdate and now in mysql?

The NOW() function gives current datetime as a timestamp while CURDATE() gives only current date, not time.


1 Answers

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.

like image 54
paxdiablo Avatar answered Sep 24 '22 14:09

paxdiablo