CODEWITHSUNDEEP
mysqlphpmyadmin
Kapil Sharma
Kapil Sharma

Reputation: 10417

MySQL default datetime through phpmyadmin

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:

Error screenshot

Can someone please suggest why I'm getting that error and how to fix that?

Upvotes: 24

Views: 127074

Answers (5)

Ashutosh Bajpai
Ashutosh Bajpai

Reputation: 73

Set the type of the field as TIMESTAMP too.

enter image description here

Upvotes: 4

Lucas Moyano Angelini
Lucas Moyano Angelini

Reputation: 368

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();

Upvotes: 1

user1518659
user1518659

Reputation: 2246

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());

Upvotes: 3

Mithun Sreedharan
Mithun Sreedharan

Reputation: 51282

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.

Upvotes: 31

Nanne
Nanne

Reputation: 64409

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.

Upvotes: 2

Related Questions