a-one a-one - 5 months ago 55
MySQL Question

error for sysdatetime()

+-------------------------+
| Tables_in_movierentaldb |
+-------------------------+
| MEMBERSHIP |
| PRICE |
| RENTAL |
| movie |
| video |
+-------------------------+

mysql> describe rental
-> ;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| RENT_NUM | int(11) | NO | PRI | NULL | |
| MEM_NUM | int(11) | YES | MUL | NULL | |
+-----------+---------+------+-----+---------+-------+


Hello stackoverflow community,
I have an issue when creating a table in mysql, I am trying to enter :

alter table RENTAL add RENT_DATE datetime default SYSDATETIME();


and I am getting a syntax error.

the reason I did not enter when creating the table because I had the same syntax issue when trying to enter it in the create table command:

CREATE TABLE RENTAL (
RENT_NUM int PRIMARY KEY,
RENT_DATE DATE DEFAULT SYSDATETIME(),
MEM_NUM int CONSTRAINT RENTAL_MEM_NUM_FK REFERENCES MEMBERSHIP);


I had the same issue with the constraint MEM_NUM but when I entered the following command it was able to create the constraint:

alter table RENTAL add foreign key (MEM_NUM) references
MEMBERSHIP(MEM_NUM);


Thanks for your time!

Answer

You have to use:

alter table RENTAL add RENT_DATE datetime default CURRENT_TIMESTAMP; 

The DEFAULT value clause in a data type specification indicates 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 TIMESTAMP and DATETIME columns. See Section 12.3.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

DOCs here