MySQL error - creating table and trying current date as default

Question

CREATE TABLE payment.payment_details ( customer_id INT NOT NULL AUTO_INCREMENT, amount INT NOT NULL, mode VARCHAR(50) NOT NULL, payment_date DATE NOT NULL DEFAULT CURRENT_DATE(), PRIMARY KEY (customer_id)) ENGINE = InnoDB;

Operation failed: There was an error while applying the SQL script to the database. Executing: CREATE TABLE payment.payment_details ( customer_id INT NOT NULL AUTO_INCREMENT, amount INT NOT NULL, mode VARCHAR(50) NOT NULL, payment_date DATE NOT NULL DEFAULT CURRENT_DATE(), PRIMARY KEY (customer_id)) ENGINE = InnoDB;

ERROR 1064: 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 'CURRENT_DATE(), PRIMARY KEY (customer_id)) ENGINE = InnoDB' at line 5 SQL Statement: CREATE TABLE payment.payment_details ( customer_id INT NOT NULL AUTO_INCREMENT, amount INT NOT NULL, mode VARCHAR(50) NOT NULL, payment_date DATE NOT NULL DEFAULT CURRENT_DATE(), PRIMARY KEY (customer_id))

prasad_ · Accepted Answer · 2023-04-10 06:26:21Z

1

The error in the mysql client:

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 'CURRENT_DATE, PRIMARY KEY (customer_id) ) ENGINE = InnoDB' at line 5 ...

This is related to the invalid syntax in your column definition for the payment_date. The correct syntax would be:

payment_date DATE NOT NULL DEFAULT (CURRENT_DATE),

This worked fine with MySQL Server version 8.0.30.

The following two are just the same as above:

payment_date DATE NOT NULL DEFAULT (CURRENT_DATE()),
payment_date DATE NOT NULL DEFAULT (CURDATE()),

answered Apr 10, 2023 at 6:26

prasad_

14.4k2 gold badges27 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nidhi_7 · Accepted Answer · 2023-04-10 05:06:57Z

0

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, for TIMESTAMP and DATETIME columns, you can specify CURRENT_TIMESTAMP as the default

You can't set the default value on DATE type in MySQL, as per the documentation.

You can use TIMESTAMP instead and set CURRENT_TIMESTAMP as a default value.

answered Apr 10, 2023 at 5:06

nidhi_7

175 bronze badges

1 Comment

tobifasc Over a year ago

This appears to only be true for MySQL versions < 8. You could add that to your answer.

Collectives™ on Stack Overflow

MySQL error - creating table and trying current date as default

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related