Question 20141 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL error – creating table and trying current date as default

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

Tags:

2

Answers


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

    Login or Signup to reply.
  2. 0

    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()),
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search