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.
