Insert a date in MySQL only if it is valid

Question

I have a table with date values stored as strings, like '2012-01-15'. Some of them are invalid, like '2012-04-31'. I would like to insert the valid dates into a DATE type column in another table, and default the day to 1 if it is too large for the month.

DAYNAME seems to be the only function in MySQL that will check whether a date is valid. However, it issues a warning for an invalid date (in addition to returning NULL), which upgrades to an error in an INSERT or UPDATE statement.

So I'd like to do something like

INSERT INTO date_tbl (date_value)
SELECT IF(DAYNAME(date_string) IS NOT NULL, date_string, CONCAT(LEFT(date_string, 8), '1')
FROM date_string_table;

This fails with Data truncation: Incorrect datetime value: '2010-04-31' even though I am not actually inserting invalid data.

The problem with using INSERT IGNORE is running the risk of actually inserting invalid data, which I would really like to avoid.

---

EDIT Oct 5:

This problem can be reproduced without creating the intermediate table simply as

CREATE TABLE date_tbl (
    date_val DATETIME
);

INSERT INTO date_tbl (date_val)
SELECT IF(DAYNAME('2012-04-31') IS NOT NULL, '2012-04-31', NULL);

I would like the above INSERT to insert NULL for that invalid date, instead of failing.

Answer 1

You can compare days of the proper date and last day for that month with LAST_DAY and STR_TO_DATE.

So your query would be:

INSERT INTO date_tbl (date_val)
SELECT IF(DAY(STR_TO_DATE('2012-02-30','%Y-%m-%d')) > DAY(LAST_DAY(STR_TO_DATE('2012-02-30','%Y-%m-%d'))), NULL,'2012-02-30');

DB Fiddle

Answer 2

A workaround is to use INSERT IGNORE and then validate after the fact:

SELECT date_value
FROM date_tbl
WHERE DAYNAME(date_value) IS NULL;

Should return zero rows.