date(now()) giving "'(' is not valid at this position, expecting 'text'" in MySQL 8.0.22

Question

I am very new to learning SQL and database creation/management and I'm running into an issue that I can't seem to find an existing answer for. I'm using MySQL to learn right now and I'm running version 8.0.22. I'm trying to use the date function to extract the date portion of the result from now() to use as a default value, but it's giving me the error specified in the title.

I have tried a couple things and they haven't seemed to change anything.

This is what I tried first.

INV_DATE DATE DEFAULT date(now()) NOT NULL,

I then tried it like:

INV_DATE DATE DEFAULT date'2016-01-01' NOT NULL,

Just to see if that worked syntactically and it did. NOW() on its own works when I change the DATE datatype to DATETIME.

I'm just not really sure what's going on here with this function.

Any help would be appreciated!

Edit: This is the context in which this is being run.

CREATE TABLE INVOICE (
INV_NUMBER INTEGER,
INV_DATE DATE DEFAULT now() NOT NULL,
PRIMARY KEY(INV_NUMBER),
FOREIGN KEY (CUS_CODE) REFERENCES CUSTOMER (CUS_CODE) ON UPDATE CASCADE);

The reason I'm trying to use now() as a default value is because the learning material I'm using jumps between dialects to demonstrate things and is using Oracle SQL for this part. It shows:

INV_DATE DATE DEFAULT SYSDATE NOT NULL,

And I was trying to convert it into the MySQL equivalent.

@tcadidot0 Yes, this is one of the attributes being defined in a table. — Eth, Dec 29 '20 at 08:22
I am familiar with `TIMESTAMP` or `DATETIME` datatype having `DEFAULT` value of `NOW()` or `CURRENT_TIMESTAMP()` in MySQL but I don't think there's one for `DATE` value. [Fiddle for further tests](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4cf746abde01f8a6afb900860482b6de). — FanoFN, Dec 29 '20 at 08:32
@tcadidot0 Gotcha. With that being the case, the date function that I was trying to use originally should be used with timestamp literals? Like date('2016-01-01 23:50:26') and not with a function as the expression being passed in? Such as date(now()). I only ask because I have seen date(now()) in a ton of examples online. — Eth, Dec 29 '20 at 08:47
If you need a column of DATE datatype which' default value is current date you may use either generated column or trigger. — Akina, Dec 29 '20 at 08:55
Please share the full query you are using, not just some parts — Nico Haase, Dec 29 '20 at 08:57
For queries, there's no problem using `DATE(NOW())`. I think that was the one you see being used in examples. See this https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8e8da7c5fabe82bdb0ca9a7d29dea80f — FanoFN, Dec 29 '20 at 08:57
@NicoHaase I just included it. I had accidentally formatted it wrong in my original edit, so it didn't show fully. — Eth, Dec 29 '20 at 09:02
@tcadidot0 *For queries, there's no problem using DATE(NOW())* Using DATETIME column instead of DATE may result in increased query complexity when this column must be used in some complex condition. As a final result this may decrease performance. — Akina, Dec 29 '20 at 09:02
@tcadidot0 Yes, I understand now. It was specifically in the way I was using it that was causing the issue. — Eth, Dec 29 '20 at 09:03
Ah yes @Akina , thanks for clarifying that. Probably what I meant was using `DATE(NOW())` in between creating table and using it as a query. Sorry for that. — FanoFN, Dec 29 '20 at 09:09

Popeye · Accepted Answer · 2020-12-29T08:55:06.973

1

Rule - now() is not allowed as a default value for the date data type column.

Also, If you want to use now() in your query then you don't need date(now()) only now() is enough.

Check the db<>fiddle to see the error with now() as default value and without it.

edited Dec 29 '20 at 08:55

answered Dec 29 '20 at 08:10

Popeye

35,427
4
10
31

Okay, thanks. The learning material I'm using switches between different sql dialects to teach its concepts and this particular example was using Oracle sql. It was:INV_DATE DATE DEFAULT SYSDATE NOT NULL Does that not convert over to MySQL in this case since now() can't be used? – Eth Dec 29 '20 at 08:17
*Rule - now() is not allowed as a default value for the column.* - this is true for DATE datatype column only. But it is valid for DATETIME/TIMESTAMP column and even for numeric/string datatypes (in a form `DEFAULT (now())`). https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=988abb8a5d857dd82cfb7e6e0e620d1d – Akina Dec 29 '20 at 08:51
@Akina Thanks! That makes sense. I think I'm understanding what the problem was now. – Eth Dec 29 '20 at 08:53
Yes @Akina, Sample code is based on the DATE data type. so mentioned it. but let me mention it here. – Popeye Dec 29 '20 at 08:54
@Akina I haven't gotten to triggers yet, so I have some more learning to do. My original question has been answered so I'm going to accept this answer. – Eth Dec 29 '20 at 09:06
Check this answer: https://stackoverflow.com/questions/20461030/current-date-curdate-not-working-as-default-date-value/62148285#62148285 – t1f Oct 28 '21 at 12:34

score 1 · Answer 2 · answered Dec 29 '20 at 13:13

Unfortunately, MySQL does not allow default values for date columns. You have two reasonable alternatives.

The first is to use a trigger to assign the date value when you insert new rows.

The second is to use a datetime column along with a generated column:

CREATE TABLE INVOICES (
    INV_NUMBER INTEGER PRIMARY KEY,
    INV_DATETIME DATETIME DEFAULT NOW(),
    INV_DATE DATE GENERATED ALWAYS AS (DATE(INV_DATETIME))
);

You can use a view (or other mechanisms) to hide the INV_DATETIME column. However, you need to update INV_DATETIME because INV_DATE is generated based on that column.

date(now()) giving "'(' is not valid at this position, expecting 'text'" in MySQL 8.0.22

2 Answers2