Set NOW() as Default Value for datetime datatype?

by

I have two columns in table users namely registerDate and lastVisitDate which consist of datetime data type. I would like to do the following.

  1. Set registerDate defaults value to MySQL NOW()
  2. Set lastVisitDate default value to 0000-00-00 00:00:00 Instead of null which it uses by default.

Because the table already exists and has existing records, I would like to use Modify table. I’ve tried using the two piece of code below, but neither works. 

ALTER TABLE users MODIFY registerDate datetime DEFAULT NOW()
ALTER TABLE users MODIFY registerDate datetime DEFAULT CURRENT_TIMESTAMP;

It gives me Error : ERROR 1067 (42000): Invalid default value for 'registerDate'

Is it possible for me to set the default datetime value to NOW() in MySQL?

13 Answers
13

Leave a Comment