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

How to set default DATE when creating table in mysql 8.0.32?

I am trying to create a table with a column with a DATE type. I would like to set the default value for this field to be CURRENT_DATE.
This does not work as expected, especially when compared to doing the same thing with a TIMESTAMP.

This is the problem:

mysql> CREATE TABLE expenses (expense_date DATE DEFAULT CURRENT_DATE);
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)' at line 1

Whereas doing the exact same thing with timestamp works fine:

mysql> CREATE TABLE expenses (expense_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.02 sec)

Both current_date and current_timestamp function as expected outside of table creation:

mysql> select current_date;
+--------------+
| current_date |
+--------------+
| 2023-03-20   |
+--------------+
1 row in set (0.00 sec)

mysql> select current_timestamp;
+---------------------+
| current_timestamp   |
+---------------------+
| 2023-03-20 01:33:27 |
+---------------------+
1 row in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32    |
+-----------+
1 row in set (0.00 sec)

I think this must be a bug in MySQL 8.0.32; I cannot find a way to set the default date to the current date.

I am trying to create a table with a DATE column and have it set the default value to the current_date, but this does not work.

I have also tried changing the column to a TEXT type and this also doesn’t work.
I have tried CREATE TABLE expenses (expense_date DATE DEFAULT CAST(CURRENT_DATE, DATE);

I can’t get the current date to be set as the default in any way.

Tags:

2

Answers


  1. 0

    CURRENT_TIMESTAMP with date works just fine.

    Just insert some values and see.

    [temp]> CREATE TABLE expenses (expense_date DATE DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.042 sec)

 [temp]> show create table expenses;                                     +----------+--------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                       |
+----------+--------------------------------------------------------------------------------------------------------------------+
| expenses | CREATE TABLE `expenses` (
  `expense_date` date DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)
    Login or Signup to reply.
  2. 0

    Quoting from the MySQL reference:

    Explicit Default Handling as of MySQL 8.0.13 The default value specified in a DEFAULT clause can be a literal constant or an
    expression. With one exception, enclose expression default values
    within parentheses to distinguish them from literal constant default
    values.

    Since CURRENT_DATE is just a synonym for CURDATE() it’s an expression, so

    CREATE TABLE expenses (expense_date DATE DEFAULT (CURRENT_DATE));

    works just fine. See https://www.db-fiddle.com/f/piXEi5b69uFemSR2xzk2DK/0

    Note that CURRENT_TIMESTAMP is the one exception referred to above, and the enclosing parentheses are not required, so

    CREATE TABLE expenses (expense_date DATE DEFAULT CURRENT_TIMESTAMP);

    also works.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search