How to set default date time as system date time in mysql

Question

I am trying to set my columns default date time to system datetime. It shows me an error

Invalid default value for 'InsertionDate'

alter table `vts`.`tblpickpoint` 
  add column `InsertionDate` 
      datetime DEFAULT 'Now()' NULL after `PickPointLatLong`

Even if you could use a function as default value (you cannot) you aren't providing a function: `'Now()'` is a string. — Álvaro González, Apr 05 '10 at 10:27

score 3 · Accepted Answer · answered Apr 05 '10 at 09:57

The default value for a column in mysql cannot be the result of a function.

The one exception is the current_timestamp as astander points out.

Your statement should be

alter table `vts`.`tblpickpoint` 
  add column `InsertionDate` TIMESTAMP 
             DEFAULT CURRENT_TIMESTAMP

score 2 · Answer 2 · answered Apr 05 '10 at 09:55

2

Have a look at CURRENT_TIMESTAMP

answered Apr 05 '10 at 09:55

Adriaan Stander

162,879
31
289
284

score 0 · Answer 3 · answered Apr 05 '10 at 10:05

If you want to init and update the value on every change, use this:

alter table `vts`.`tblpickpoint` 
  add column `InsertionDate` 
       TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  after `PickPointLatLong`

If you only want the creation time, use this:

alter table `vts`.`tblpickpoint` 
  add column `InsertionDate` 
       TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  after `PickPointLatLong`

How to set default date time as system date time in mysql

3 Answers3