MySQL default datetime through phpmyadmin

Question

In an existing database  I ve age column  INT   Now I need to set it as dob  DATETIME    I try doing so through PHPMyAdmin  giving CURRENT TIMESTAMP as default value as defined by answer with 138 upvotes  However PHPMyAdmin is complaining  1067 - invalid default value for  dob  as in attached screenshot     Can someone please suggest why I m getting that error and how to fix that

User · Answer

Set the type of the field as TIMESTAMP too

User · Answer

You can t set CURRENT TIMESTAMP as default value with DATETIME    But you can do it with TIMESTAMP   See the difference here   Words from this blog  The DEFAULT value clause in a data type specification indicates a default value for a column  With one exception  the default value must be a constant  it cannot be a function or an expression   This means  for example  that you cannot set the default for a date column to be the value of a function such as NOW   or CURRENT DATE    The exception is that you can specify CURRENT TIMESTAMP as the default for a TIMESTAMP column

User · Answer

I don t think you can achieve that with mysql date  You have to use timestamp or try this approach    CREATE TRIGGER table OnInsert BEFORE INSERT ON  DB   table  FOR EACH ROW SET NEW dateColumn   IFNULL NEW dateColumn  NOW

User · Answer

You re getting that error because the default value current time is not valid for the type DATETIME  That s what it says  and that s whats going on   The only field you can use current time on is a timestamp

User · Answer

The best way for DateTime is use a Trigger                 ROLE               drop table if exists  role   create table  role         id role  bigint 20  unsigned not null auto increment       date created  datetime       date deleted  datetime       name  varchar 35  not null       description  text      primary key   id role     comment      drop trigger if exists  role date created   create trigger  role date created  before insert     on  role      for each row      set new  date created    now

[mysql] MySQL default datetime through phpmyadmin

Examples related to mysql

Examples related to phpmyadmin