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
)

_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. — Rick James, Nov 21 '18 at 19:11

score 87 · Accepted Answer · edited Mar 20 '23 at 02:23

87

[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

edited Mar 20 '23 at 02:23

Tangentially Perpendicular

5,012
4
11
29

answered Dec 09 '13 at 00:19

zerkms

249,484
69
436
539

@inControl: yep, using `DATE_FORMAT` – zerkms Dec 09 '13 at 00:33
38

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. – Dan Lugg Mar 31 '16 at 13:52
12

@NorthbornDesign I solved it for myself by migrating to postgresql :-) – zerkms Mar 31 '16 at 19:47
1

please note that the answer from @NorthbornDesign is the correct one :) – Yehosef Jul 07 '16 at 08:48
For a `change_date` column (update and delete): `CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);` https://dev.mysql.com/doc/refman/5.5/en/timestamp-initialization.html – Ralf Hundewadt Sep 03 '18 at 09:51
1

the 5th Answer is the correct one as of October 2021. It works! – t1f Oct 28 '21 at 12:31

Amos Long · Answer 2 · 2021-12-14T05:50:46.473

50

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.

edited Dec 14 '21 at 05:50

answered Aug 06 '18 at 13:56

Amos Long

845
11
14

3

It worked that way for me, thanks. (look at the mysql version before). – Mylon Dec 11 '21 at 15:06
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()`). – Barmar Jun 01 '23 at 17:49

score 31 · Answer 3 · answered Jun 02 '20 at 08:53

31

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.

answered Jun 02 '20 at 08:53

t1f

3,021
3
31
61

3

Correct, Important to Note is the Extra Brackets, thanks alot – Muhammad Faizan Khatri Dec 14 '20 at 17:24
@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! – t1f Dec 14 '20 at 22:01
1

This worked in MySQL 8.0.21. Weird syntax tho. Thank you! – The Onin Jan 05 '21 at 21:21
1

@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. – t1f Jan 05 '21 at 22:18
1

This should now be marked as the correct answer. – Ethan Allen Apr 13 '21 at 01:18
1

Muchísimas gracias por tu solución!! – Alexa Oct 28 '21 at 03:47
Not working for me – Apr 23 '22 at 21:58
I was wrong! I apologise – Apr 24 '22 at 19:17
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()) ? – Nertan Lucian Dec 13 '22 at 16:08
1

@NertanLucian Can't remember offhand but it's somewhere in the documentation if you give it a read. – t1f Dec 13 '22 at 22:54

score 27 · Answer 4 · answered May 12 '14 at 22:00

27

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 ;

answered May 12 '14 at 22:00

user3630264

381
3
3

I had to set field to `NULL` to make this work, otherwise it was inserting `0000-00-00` instead. MySQL v5.6.12. – lolbas Jun 29 '17 at 05:37

score 16 · Answer 5 · edited Mar 31 '18 at 21:15

16

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

(MySQL 5.7)

edited Mar 31 '18 at 21:15

answered Mar 31 '18 at 20:42

Brian Purgert

159
1
3

seems extra brackets around `(CURRENT_TIMESTAMP)` required in recent version of mysql. – Sushil Feb 10 '23 at 05:10

score 7 · Answer 6 · answered Jul 03 '20 at 11:23

7

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.

answered Jul 03 '20 at 11:23

aworkinghuman

128
1
8

1

I've tried this and successful. I think this should be the accepted answer – Dika Aug 19 '21 at 02:27
Agreed. Best answer here. – Kristoffer Tølbøll Jun 21 '22 at 20:31

score 4 · Answer 7 · answered Nov 21 '18 at 19:08

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

score 3 · Answer 8 · edited May 23 '17 at 12:34

3

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.

edited May 23 '17 at 12:34

Community

1
1

answered Dec 09 '13 at 00:22

Niels Keurentjes

41,402
9
98
136

1

The restriction on the number of automatic timestamps was lifted [in MySQL 5.6](http://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-5.html). – Michael - sqlbot Dec 09 '13 at 04:33
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. – Niels Keurentjes Dec 09 '13 at 09:36
1

MariaDB 10.2.1 allows `DEFAULT(expression)`. See [Create table](https://mariadb.com/kb/en/mariadb/create-table/) . – Rick James Jul 04 '17 at 01:58
You should add an answer @RickJames – Peter Chaula Nov 21 '18 at 11:01

score 3 · Answer 9 · answered Mar 04 '20 at 13:53

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

worked for me `DEFAULT LAST_DAY(CURDATE())` even without parentheses. some phpmyadmin functions might trigger error though — Hebe, Jan 05 '22 at 11:26

score 1 · Answer 10 · answered Apr 24 '22 at 18:11

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

CURRENT_DATE/CURDATE() not working as default DATE value

10 Answers10

Linked

Related