Setting default value for DATE type column to current date without time part?

Question

NOTE: The question is about DATE type, not Datetime nor Timestamp

How to alter column of date data type to use current date by default? I saw a lot of examples for datetime (with time part), but not for date. I have tried:

ALTER TABLE `accounting` ALTER `accounting_date` 
  SET DEFAULT CURRENT_DATE;
ALTER TABLE `accounting` CHANGE `accounting_date` 
  `accounting_date` DATE NOT NULL DEFAULT CURRENT_DATE;

I also tried with CURDATE(), NOW(), CURRENT_DATE() ...

duplicate of http://stackoverflow.com/questions/5818423/set-now-as-default-value-for-datetime-datatype — DevDonkey, Mar 02 '15 at 08:51
@MattHolbrook-Bull The example is about TIMESTAMP (datetime) column. My question is about DATE column — Dmitrij Kultasev, Mar 02 '15 at 09:00
so this one then? http://stackoverflow.com/questions/168736/how-do-you-set-a-default-value-for-a-mysql-datetime-column — DevDonkey, Mar 02 '15 at 09:04
datetime == date surely. youll want it as a datetime object so you can manipulate it later? — DevDonkey, Mar 02 '15 at 09:21
datetime<>date. I know the difference in data types. I do not need Time part and I do not want to cast it every time when I would output the value. — Dmitrij Kultasev, Mar 02 '15 at 09:24

score 7 · Answer 1 · edited Oct 17 '21 at 07:03

7

I use version 8.0.26, this is working:

datecolumn date DEFAULT (CURDATE())

It does not work, if you don't use the brackets!

edited Oct 17 '21 at 07:03

Mark Rotteveel

100,966
191
140
197

answered Oct 16 '21 at 14:05

Klemens

79
1
1

1

wrapping up with bracket, did the work. Thanks! – Parthasarathy S Feb 07 '22 at 16:27
It works well in Mysql too. – Mahamat Jan 10 '23 at 17:15

score 5 · Answer 2 · answered Jun 10 '20 at 16:09

5

MySQL 8.0+:

CREATE TABLE foo (
    `creation_time`     DATE DEFAULT (DATE_FORMAT(NOW(), '%Y-%m-%d'))
)

answered Jun 10 '20 at 16:09

Can Berkol

158
4
10

1

why do you use formatting when saving? formatting is normally used when you output the value – Dmitrij Kultasev Aug 10 '20 at 08:37
well, NOW() also outputs a value. In that sense DATE_FORMAT() and NOW() and other value outputting functions are not much different. DATE type accepts a string in Y-m-d format or a DATE object; where as NOW() outputs DATETIME object and its string representation includes time. there are many other options to accomplish this, but in this case what we do is, we set default value of creation time to this moment's date part. Basically we strip the time part of the NOW() output and create a Y-m-d string and set it to creation_time. – Can Berkol Sep 03 '20 at 11:26
1

I understand what does DATE_FORMAT functions do in general, however in your example it's just redundant and even more, it is hurting performance. You can leave just NOW() without formatting and it will simply work with the same output. – Dmitrij Kultasev Sep 03 '20 at 11:43
Having NOW() was issuing errors to me, maybe I was making some other mistake that I wasn't aware of... It's been a while and I don'T remember what exactly was the issue. However, due to that experience, I had the above statement as a work around at that time. Nonetheless, you are right about the performance especially for high INSERT loads and yes, DATE_FORMAT() is redundant. Also, yes I tried couple minutes ago and having only NOW() also works. – Can Berkol Sep 04 '20 at 12:49

score 4 · Accepted Answer · edited May 23 '17 at 12:13

Probably you cannot set default value for 'date' data type in mysql. You need to change the type to timestamp or datetime.

You may have a look at this similar question.

Invalid default value for 'Date'

EDIT:

In version 5.6.5, it is possible to set a default value on a datetime column, and even make a column that will update when the row is updated. The type definition:

CREATE TABLE foo (
    `creation_time`     DATETIME DEFAULT CURRENT_TIMESTAMP,
    `modification_time` DATETIME ON UPDATE CURRENT_TIMESTAMP
)

Reference: http://optimize-this.blogspot.com/2012/04/datetime-default-now-finally-available.html

He literally said Date, not Datetime – Kevin Feb 22 '19 at 04:52 — Kevin, Feb 22 '19 at 04:52

score 1 · Answer 4 · answered Dec 16 '18 at 10:40

As noted in this question Invalid default value for 'create_date' timestamp field, this error may happen when MySQL is in strict mode (which is default behavior, I believe).

If you want to override it, just disable all these checks when creating your table:

SET SQL_MODE='ALLOW_INVALID_DATES';

The warning will be still generated, however it will allow to create the table.

score 1 · Answer 5 · answered Aug 06 '20 at 15:03

1

It seems to work in sqlite:

"date" DATE NOT NULL DEFAULT (DATE(CURRENT_TIMESTAMP))

answered Aug 06 '20 at 15:03

yital9

6,544
15
41
54

it does not work – The EasyLearn Academy May 31 '22 at 07:24
I used a value instead like this : ``` not null DEFAULT '1000-01-01' ``` – Himanshu Suthar Mar 31 '23 at 06:32

score 0 · Answer 6 · answered May 31 '22 at 07:27

No, you cannot. The documentation is pretty clear on this:

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 TIMESTAMP and DATETIME columns.

Setting default value for DATE type column to current date without time part?

6 Answers6

Linked