哪些值使从VARCHAR到DATE的转换失败? [英] Which values are making the conversion from VARCHAR to DATE fail?
问题描述
我正在从键/值表中读取一个值,该值应包含格式为 yyyy-MM-dd
的NULLABLE日期.
I am reading a value from a key/value table that should contain a NULLABLE date with the format yyyy-MM-dd
.
查询的简化版本是
SELECT CAST(ClaimDate AS DATE) AS ClaimDate
FROM MyTable
这会导致错误消息:
从字符转换日期和/或时间时转换失败字符串.
Conversion failed when converting date and/or time from character string.
我也尝试了 CONVERT(DATE,ClaimDate)
,但无济于事.
I have tried CONVERT(DATE, ClaimDate)
as well to no avail.
现在,除了知道为什么会出现此错误外,我还想知道是哪些值导致了此错误.
Now, more than knowing why I get this error, I am curious to know which values are causing this.
我已经看到有关上述错误消息的几个问题.但是我还没有找到能回答我问题的人.
I have seen several questions regarding the error message above. But I haven't found the one giving an answer to my problem.
当然,除了提出差劲的价值外,提出解决方案也是很受赞赏的.
Of course, suggesting a solution in addition to getting bad values is highly appreciated.
推荐答案
Depending on your SQL Server version (2012+), you can use TRY_CONVERT:
SELECT ClaimDate
FROM MyTable
WHERE TRY_CONVERT(DATE, ClaimDate ) IS NULL
AND ClaimDate IS NOT NULL
这将为您提供无法转换为日期的值,如何解决这些问题是另一个问题.另外,可以转换 2019-01-12
的值,但是如果它是字符串,则不能确定是12月还是1月.您可能会得到有效但错误的日期!
This will give you the values that can't be converted to a date, how you're going to solve those issues is another question. Also, a value of 2019-01-12
may be converted, but if it's in a string you cannot be sure whether this is a date in December or in January. You may get valid but wrong dates!
这篇关于哪些值使从VARCHAR到DATE的转换失败?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!