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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

create table the easy way    capture ts DATETIME DEFAULT CURRENT TIMESTAMP    capture dt DATE AS  DATE capture ts        MySQL 5 7

User · Answer

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  gt  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  gt  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  gt  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  gt  SELECT VERSION     ---------------------------    VERSION                      ---------------------------    10 3 18-MariaDB-0 deb10u1    ---------------------------  1 row in set  0 010 sec   MariaDB  niffdb  gt

User · Answer

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

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

Examples related to mysql

Examples related to date

Examples related to default-value