CODEWITHSUNDEEP
mysqlmysql-5.7
Bhawan
Bhawan

Reputation: 2491

datetime error in generated columns in MySQL

I have a table 'tbl1' in which there are two fields:

  1. created_at of type datetime and NULL is allowed.
  2. num_days of type INT(11) and NULL is allowed and default value is 3.

I am trying to create a generated column 'cut_off' of type date and column of STORED type.

I am using this command:

alter table tbl1 add column cut_off date GENERATED ALWAYS AS (DATE(created_at + num_days)) STORED;

I am getting this error:

ERROR 1292 (22007): Incorrect datetime value: '20181119063562'

But in this query:

select distinct(DATE(created_at + num_days)) from tbl1;

is running fine and giving no errors.

Any help would be highly appreciated.

Upvotes: 1

Views: 239

Answers (1)

Bobert1234
Bobert1234

Reputation: 89

you are adding seconds not days, and a minute cannot have 62 seconds

perhaps try 

DATE(created_at + INTERVAL num_days DAY)

Upvotes: 2

Related Questions