[mysql] CURRENT_DATE/CURDATE() not working as default DATE value

Pretty straight forward question here, I think this should work but it doesn't. Why doesn't it?

CREATE TABLE INVOICE(
   INVOICEDATE DATE NOT NULL DEFAULT CURRENT_DATE
)

I have the current latest version of MySQL: 8.0.20

So my table name is visit, my column name is curdate.

alter table visit modify curdate date not null default (current_date);

This writes the default date value with no timestamp.

create table the_easy_way(
  capture_ts DATETIME DEFAULT CURRENT_TIMESTAMP,
  capture_dt DATE AS (DATE(capture_ts))
)

(MySQL 5.7)

----- 2016-07-04 MariaDB 10.2.1 -- Release Note -- -----

Support for DEFAULT with expressions (MDEV-10134).

----- 2018-10-22 8.0.13 General Availability -- -- -----

MySQL now supports use of expressions as default values in data type specifications. This includes the use of expressions as default values for the BLOB, TEXT, GEOMETRY, and JSON data types, which previously could not be assigned default values at all. For details, see Data Type Default Values.

declare your date column as NOT NULL, but without a default. Then add this trigger:

USE `ddb`;
DELIMITER $$
CREATE TRIGGER `default_date` BEFORE INSERT ON `dtable` FOR EACH ROW
if ( isnull(new.query_date) ) then
 set new.query_date=curdate();
end if;
$$
delimiter ;

I came to this page with the same question in mind, but it worked for me!, Just thought to update here , may be helpful for someone later!!

MariaDB [niffdb]> desc invoice;
+---------+--------+------+-----+---------+----------------+
| Field   | Type   | Null | Key | Default | Extra          |
+---------+--------+------+-----+---------+----------------+
| inv_id  | int(4) | NO   | PRI | NULL    | auto_increment |
| cust_id | int(4) | NO   | MUL | NULL    |                |
| inv_dt  | date   | NO   |     | NULL    |                |
| smen_id | int(4) | NO   | MUL | NULL    |                |
+---------+--------+------+-----+---------+----------------+
4 rows in set (0.003 sec)

MariaDB [niffdb]> ALTER TABLE invoice MODIFY inv_dt DATE NOT NULL DEFAULT (CURRENT_DATE);
Query OK, 0 rows affected (0.003 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [niffdb]> desc invoice;
+---------+--------+------+-----+-----------+----------------+
| Field   | Type   | Null | Key | Default   | Extra          |
+---------+--------+------+-----+-----------+----------------+
| inv_id  | int(4) | NO   | PRI | NULL      | auto_increment |
| cust_id | int(4) | NO   | MUL | NULL      |                |
| inv_dt  | date   | NO   |     | curdate() |                |
| smen_id | int(4) | NO   | MUL | NULL      |                |
+---------+--------+------+-----+-----------+----------------+
4 rows in set (0.002 sec)

MariaDB [niffdb]> SELECT VERSION();
+---------------------------+
| VERSION()                 |
+---------------------------+
| 10.3.18-MariaDB-0+deb10u1 |
+---------------------------+
1 row in set (0.010 sec)

MariaDB [niffdb]>

According to this documentation, starting in MySQL 8.0.13, you will be able to specify:

CREATE TABLE INVOICE(
    INVOICEDATE DATE DEFAULT (CURRENT_DATE)
)

Unfortunately, as of today, that version is not yet released. You can check here for the latest updates.

Currently from MySQL 8 you can set the following to a DATE column:

In MySQL Workbench, in the Default field next to the column, write: (curdate())

If you put just curdate() it will fail. You need the extra ( and ) at the beginning and end.

As the other answer correctly notes, you cannot use dynamic functions as a default value. You could use TIMESTAMP with the CURRENT_TIMESTAMP attribute, but this is not always possible, for example if you want to keep both a creation and updated timestamp, and you'd need the only allowed TIMESTAMP column for the second.

In this case, use a trigger instead.