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

user_14905791 0
Viewed 487

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.

mysql sql
2 Answers

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.

user_115656290

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.

user_11440350
Related