level20peon level20peon - 11 days ago 5
MySQL Question

How to remove invalid date values in MariaDB 10.1?

I have one table with one datecolumn

foo (before):

+------------+
| datecolumn |
+------------+
| 1939-04-06 |
| 1949-02-30 |
+------------+


I want to set the dates to NULL if they are invalid.

foo (after):

+------------+
| datecolumn |
+------------+
| 1939-04-06 |
| NULL |
+------------+


In MySQL (5.7) I could just use this trick in order to accomplish just that:

ALTER TABLE `foo` MODIFY COLUMN `datecolumn` varchar(255) NULL DEFAULT NULL FIRST;
SET SQL_MODE='ALLOW_INVALID_DATES';
ALTER TABLE `foo` MODIFY COLUMN `datecolumn` date NULL DEFAULT NULL FIRST;
SET SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
UPDATE `foo` SET `datecolumn` = NULL WHERE `datecolumn` = '0000-00-00';


However, this does not seem to work in MariaDB 10.1, regardless of what I tried:

1) various SQL_MODE's

2) various STR_TO_DATE or DATE_FORMAT approaches (I hoped to get NULL I converted the invalid date-string to a date)

Could anybody help me to achieve my goal?

Answer

This should work:

UPDATE foo SET datecolumn = CAST(datecolumn AS DATE);

Make sure that strict mode is unset in SQL_MODE, otherwise any attempt will cause an error.

Comments