CODEWITHSUNDEEP
mysqldatedefault-value
inControl
inControl

Reputation: 2344

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
)

Upvotes: 89

Views: 201893

Answers (11)

Gustavo Macedo
Gustavo Macedo

Reputation: 1

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

Upvotes: 0

zerkms
zerkms

Reputation: 255045

[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

Upvotes: 93

Vipul Verma
Vipul Verma

Reputation: 123

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

Upvotes: 0

Amos Long
Amos Long

Reputation: 1085

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.

Upvotes: 66

aworkinghuman
aworkinghuman

Reputation: 170

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.

Upvotes: 10

t1f
t1f

Reputation: 3181

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.

Upvotes: 36

Arun
Arun

Reputation: 1763

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

Upvotes: 3

Rick James
Rick James

Reputation: 142483

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

Upvotes: 3

Brian Purgert
Brian Purgert

Reputation: 159

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

(MySQL 5.7)

Upvotes: 16

user3630264
user3630264

Reputation: 381

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 ;

Upvotes: 27

Niels Keurentjes
Niels Keurentjes

Reputation: 41968

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.

Upvotes: 3

Related Questions