CURRENT_DATE/CURDATE() not working as default DATE value

Question

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
)

Betweentimes

This Question is being outdated. Newer versions allow such. See my Answer for version numbers and their release dates. That is, Updating may be the answer for you. – Marley

the 5th Answer is the correct one as of October 2021. – Scharf

Answer 1

[Edit] As of MySQl 8.0.13 it is supported. See this answer below.

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

Endocentric

@inControl: yep, using DATE_FORMAT – Endocentric

It's 2016 and, honestly, the fact that this still holds true is ridiculous. I thought the whole reason for constant synonyms was to allow them in places non-constant expressions were forbidden. – Miso

@NorthbornDesign I solved it for myself by migrating to postgresql :-) – Endocentric

please note that the answer from @NorthbornDesign is the correct one :) – Mcadoo

For a change_date column (update and delete): CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html – Cohen

the 5th Answer is the correct one as of October 2021. It works! – Scharf

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

Tamarisk

It worked that way for me, thanks. (look at the mysql version before). – Nazarene

The important thing to note is that an expression has to be inside parentheses. Without parentheses the default must be a literal (except for CURRENT_TIMESTAMP()). – Barina

Answer 3

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.

Scharf

Correct, Important to Note is the Extra Brackets, thanks alot – Lathi

@MuhammadFaizanKhatri Indeed. That little detail made me lose an entire day of searching and reading documentation. It's not clearly explained and takes some trial and error for a first time user. Glad it helped, cheers! – Scharf

This worked in MySQL 8.0.21. Weird syntax tho. Thank you! – Bairn

@NinoŠkopac Indeed. Those weird extra brackets cost me a full day of searching. Their official documentation isn't at all clear on this aspect. Guess it's the little things ^_^. Glad it worked out for you. – Scharf

This should now be marked as the correct answer. – Copyread

Muchísimas gracias por tu solución!! – Encircle

Not working for me – Winding

I was wrong! I apologise – Winding

What signifies the extra brackets? I mean, what's the difference with or without them syntax wise? Can now the brackets work with other functions like (now()) ? – Pashalik

@NertanLucian Can't remember offhand but it's somewhere in the documentation if you give it a read. – Scharf

Answer 4

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 ;

Fitful

I had to set field to NULL to make this work, otherwise it was inserting 0000-00-00 instead. MySQL v5.6.12. – Selfsealing

Answer 5

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

(MySQL 5.7)

Spectrogram

seems extra brackets around (CURRENT_TIMESTAMP) required in recent version of mysql. – Disinterested

Answer 6

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.

Albrecht

I've tried this and successful. I think this should be the accepted answer – Persistence

Agreed. Best answer here. – Genovevagenre

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

Marley

Answer 8

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.

Clipfed

The restriction on the number of automatic timestamps was lifted in MySQL 5.6. – Lurcher

Thanks for the addition, wasn't aware of that - still good to keep in mind since most webhosting is still at 5.1 or 5.5. – Clipfed

MariaDB 10.2.1 allows DEFAULT(expression). See Create table . – Marley

You should add an answer @RickJames – Haggai

Answer 9

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

Smithery

worked for me DEFAULT LAST_DAY(CURDATE()) even without parentheses. some phpmyadmin functions might trigger error though – Lactometer

Answer 10

While creating a table, you have to use CURRENT_DATE() function as default value. Please see below example I just tested.

CREATE TABLE SALES_DATA (
    SALES_ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    SALES_GIRL_ID INT UNSIGNED NOT NULL,
    SALES_DATE DATE NOT NULL DEFAULT (CURRENT_DATE()),
    TOTAL_SALES FLOAT(6, 2),
    PRIMARY KEY (SALES_ID),
    FOREIGN KEY (SALES_GIRL_ID) REFERENCES SALES_GIRLS(ID)
);

Wireworm

Answer 11

I had this same problem but I solved with the answer of

"Amos Long"

Try the code bellow:

ALTER TABLE `database`.`my_table` 
ADD COLUMN `date_insert` DATETIME DEFAULT (now());

Mcmullen