Error while assigning default value to a datetime field in mysql

Rohit Bajaj
Rohit Bajaj used Ask the Experts™
on
HI,
I am getting the following error on creation of a column with type datetime and setting the default value to NOW().
Basicaly i want to create a column in my table so that whenever a new row is inserted it automatically gets populated with the current date and time.
An error occurred when trying to change the field 'created_on' via

ALTER TABLE `retentions` CHANGE `created_on` `created_on` DATETIME
 NULL
 DEFAULT 'NOW()'

MySQL said: Invalid default value for 'created_on'

Open in new window

Whats wrong with this... how can i achieve this..

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017
Commented:
Here is an explanation/setup that will get you to where you want to be.
http://m.alvinalexander.com/mysql/mysql-default-date-now-current-date-time
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
Arnold is right.  In the table definition you can use CURRENT_TIMESTAMP.  In the SQL query you can use NOW().

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial