0

Currently I have a table in mysql with 3 columns. added_date(datetime), completion_date(date) and expired(date). completion_date and expired columns have their default value set to 0000-00-00. Now I'm trying to create an index for the table by the added_date column for which I'm using this statement:

ALTER TABLE listings ADD INDEX added_date (added_date)

This gives me an error saying Invalid default value for 'completion_date' So now to change the default value to null in my table I'm using the following command:

ALTER TABLE `listings` CHANGE `completion_date` `completion_date` DATE NULL DEFAULT NULL;

Which gives an error saying Invalid default value for 'expired' and the same vice versa. Now I've also tried creating 2 alter table statements like so:

ALTER TABLE `listings` CHANGE `completion_date` `completion_date` DATE NULL DEFAULT NULL;
ALTER TABLE `listings` CHANGE `expired` `expired` DATE NULL DEFAULT NULL;

But this again gave me the same error saying Invalid default value for 'expired'

H2O
  • 313
  • 2
  • 9
  • Addeddate is not a good value for the date column, try current_date( ) function at the place. – bigtheo Oct 29 '21 at 14:30
  • Do you mean `ALTER TABLE listings ADD INDEX current_date()`? – H2O Oct 29 '21 at 14:34
  • 1
    @H2O could you provide full table description, if you execute `ALTER TABLE listings ADD INDEX added_date (added_date)` it has nothing to do with `completion_date` – Ergest Basha Oct 29 '21 at 14:42
  • have a look at this : https://stackoverflow.com/questions/1691117/how-to-store-null-values-in-datetime-fields-in-mysql – bigtheo Oct 29 '21 at 15:01

1 Answers1

0

Do something like this to modify the column type:

create table t1 (c1 datetime not null );

alter table t1 
    modify c1 datetime null default null;

For more information, visit the docs: https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
bigtheo
  • 624
  • 9
  • 16