MySQL 无法插入 WHERE STR_TO_DATE IS NULL [英] MySQL Unable to insert WHERE STR_TO_DATE IS NULL

问题描述

我正在尝试将不是有效日期格式的 VARCHAR 字符串插入另一个表中，在该表中它们也存储为 VARCHAR.我正在尝试使用 STR_TO_DATE 来实现这一点，如果字符串的格式无效，它应该返回 NULL:

I am trying to insert VARCHARs strings which are not a valid date format into another table where they are also stored as a VARCHAR. I'm trying to use STR_TO_DATE to accomplish this, which is supposed to return NULL if a string's format is invalid:

    INSERT INTO DateErrors
    SELECT `row_id`,`date_string`
    FROM `source_table`
    WHERE STR_TO_DATE(`date_string`,'%d-%m-%Y') IS NULL

对于日期格式无效的字符串会引发错误:错误代码:1411.不正确的日期时间值:函数 str_to_date 的一些不是日期的随机字符串".我希望将这些字符串值插入到 DateErrors 中，而不是抛出此错误.

Which throws an error for strings with an invalid date format: Error Code: 1411. Incorrect datetime value: 'some random string that isnt a date' for function str_to_date. Instead of throwing this error, I want these string values inserted into DateErrors.

但是，仅运行 SELECT 查询会返回 date_string 无效的行而不会引发错误.

However, running just the SELECT query returns the rows where date_string is invalid without throwing an error.

推荐答案

您在 STR_TO_DATE

STR_TO_DATE(`date_string`,'%d-%m-%Y')

相反，应该是

STR_TO_DATE(`date_string`,'%Y-%m-%d')

这篇关于MySQL 无法插入 WHERE STR_TO_DATE IS NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！