How to set default value to sysdate in MySql?

Question

I am going to create a table with a column storing Created_date which is of datetime datatype. And my aim is to set its default value as sysdate().

I tried

CREATE TABLE tbl_table ( created_date datetime DEFAULT sysdate())

This gives me error saying not a valid default statement. I used similar logic in Oracle. Please help me to resolve this.

Thanks in advance.

Answer 1

Try CREATE TABLE tbl_table ( created_date TIMESTAMP DEFAULT NOW())

But: NOW is different from sysdate and TIMESTAMP is different from datetime, keep this in mind.

Normaly you only can use constants for default-values. TIMESTAMP is the only column-type which supports a function like NOW(). See here for further information on the MySQL Bugtracker.

Answer 2

CREATE TABLE tbl_table(     
     created_datetime      DATETIME DEFAULT   CURRENT_TIMESTAMP,
     modified_datetime     DATETIME ON UPDATE CURRENT_TIMESTAMP
)

Should do the trick.