Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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: alt text

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

Could anyone help me out here ?

like image 278
Goles Avatar asked Sep 12 '10 21:09

Goles


People also ask

What does Curdate () do in SQL?

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 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.

What is difference between timestamp and datetime in MySQL?

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts.

How can create date and time table in MySQL?

In the MySQL documentation shows the following example: CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP );


1 Answers

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
like image 98
Mark Byers Avatar answered Oct 14 '22 12:10

Mark Byers