0

I'm facing a problem with a shitty database in production done by someone before me. I have a table called "projets" with some column with a date. Instead of having a column with NULL value accepted, it was created with no NULL value authorized. So instead of a NULL, all entry are 0000-00-00. So i've change a bit the column to insert NULL value inside like this

ALTER TABLE `projets`
ALTER `date_avant_projet` DROP DEFAULT;

ALTER TABLE `projets`
CHANGE COLUMN `date_avant_projet` `date_avant_projet` DATE NULL AFTER `procede`;

So now the column can normally accept NULL value. So i try to do this to change all 0000-00-00 to NULL

update projets set date_avant_projet = NULL where date_avant_projet = '0000-00-00'

But like that i've got an SQL error 1292 : Incorrect date value: '0000-00-00' for column 'date_avant_projet' at row 1. It's very annoying for me because i have to also create a new column with a foreign key but when i try this

ALTER TABLE `projets`
ADD COLUMN `id_dernier_changement_etat` INT NOT NULL AFTER `etat`,
ADD CONSTRAINT `FK_projets_id_changement_etat` FOREIGN KEY (`id_dernier_changement_etat`) REFERENCES `changement_etat_projet` (`id`);

I've got EXACTLY the same error message : SQL error 1292 : Incorrect date value: '0000-00-00' for column 'date_avant_projet' at row 1

If someone understand better than me, it will be very helpful! Thanks in advance for your future answer.

Siick
  • 491
  • 1
  • 7
  • 23
  • 1
    About this `So instead of a NULL, all entry are 0000-00-00` read: http://stackoverflow.com/questions/3891896/blocking-0000-00-00-from-mysql-date-fields – Jorge Campos Apr 11 '17 at 14:44
  • 1
    And for your update you could convert the date to varchar and compare with `0000-00-00` – Jorge Campos Apr 11 '17 at 14:44
  • Thanks you!!!! I've done like you said! I have convert table from DATE to VARCHAR, then i've changed the value 0000-00-00 to NULL and convert again column from VARCHAR to DATE. – Siick Apr 11 '17 at 14:55
  • Glad that I could be of help :) – Jorge Campos Apr 11 '17 at 16:01

0 Answers0