MySQL STR_TO_DATE错误为NULL

I'm currently migrating a table with dates in VARCHAR columns to a new table with DATE columns. I managed to sanitize the string values in the old table to the format "YYYY-MM-DD" but when I try to perform the insert I got an error with the date "2006-04-31" because that April only had 30 days (was a typo when it was registered),

我目前正在将VARCHAR列中的日期表迁移到具有日期列的新表。我设法将旧表中的字符串值清理为“yyyyyy - mm - dd”格式，但是当我尝试执行插入时，我得到了一个日期为“2006-04-31”的错误，因为那个四月只有30天(注册时是一个输入错误)，

My question is: how can I set to NULL the column when the date is invalid without getting an error? My SQL is the following:

我的问题是:在没有错误的情况下，当日期无效时，我如何设置为NULL ?我的SQL语句如下:

INSERT INTO newFancyTable (created_at)
SELECT str_to_date(created, '%Y-%m-%d') FROM oldCrappyTable;

And the error is the following:

错误如下:

Error Code: 1292. Incorrect date value: '2006-04-31' for column 'created_at' at row 1

Thanks

谢谢

UPDATE

更新

I also tried using the following approach:

我也尝试使用以下方法:

INSERT INTO newFancyTable (created_at)
SELECT CAST(created AS DATE) FROM oldCrappyTable;

With the same error, and trying to update the oldCrappyTable would return the same:

使用相同的错误，并尝试更新oldCrappyTable将返回相同的结果:

UPDATE oldCrappyTable SET created = CAST(created AS DATE);

Both return:

都返回:

Error Code: 1292. Incorrect datetime value: '2006-04-31'

UPDATE 2

更新2

At last, I used multiple CASEs to isolate that invalid dates, in sum they were only 5 of them, Nevertheless, the issue can be reproduced by doing:

最后，我用了多个案例来隔离无效的日期，总共只有5个，但是，可以这样做来重现这个问题:

CREATE TABLE dates_temp (
  test_date DATE DEFAULT NULL
) ENGINE=MEMORY;

INSERT INTO dates_temp
SELECT STR_TO_DATE("2006-04-31", '%Y-%m-%d');

DROP TABLE dates_temp;

2 个解决方案

#1

A possible workaround is to turn off strict mode, either for the whole server, for a particular session, or for just a few statements. For example:

一种可能的解决方案是关闭严格模式(对整个服务器、对特定会话或仅对几个语句)。例如:

 set @old_sql_mode = @@sql_mode; 
 set sql_mode = ''; 
 -- Run some statements which may result in error
 set sql_mode = @old_sql_mode;

Additional Info

额外的信息

MySQL Documentation

MySQL文档

#2

In addition to @EduardoDennis answer use NULLIF to filter out zero dates:

除了@EduardoDennis的答案外，使用NULLIF过滤零日期:

INSERT INTO newFancyTable (created_at)
SELECT nullif(str_to_date(created, '%Y-%m-%d'), from_days(0)) FROM oldCrappyTable;

See my full answer here.

请看我的完整答案。

#1