CURRENT_DATE/CURDATE() not working as default DATE value

Solution 1:

It doesn't work because it's not supported

The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that 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 you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column

http://dev.mysql.com/doc/refman/5.5/en/create-table.html

Solution 2:

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

CREATE TABLE INVOICE(
    INVOICEDATE DATE DEFAULT (CURRENT_DATE)
)

MySQL 8.0.13 was released to General Availability in October 2018. The release info is located here.

Solution 3:

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 ;

Solution 4:

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.