Error while assigning default value to a datetime field in mysql

Rohit Bajaj
Rohit Bajaj used Ask the Experts™
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

MySQL said: Invalid default value for 'created_on'

Open in new window

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

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017
Here is an explanation/setup that will get you to where you want to be.
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

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