哪些值使从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.

推荐答案

根据您的SQL Server版本(2012+)，您可以使用

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屋！