哪些值使从VARCHAR转换为日期失败

Question

我正在从密钥/值表中读取一个值，该值应包含可随机的日期，并带有格式yyyy-MM-dd。

查询的简化版本是。

SELECT CAST(ClaimDate AS DATE) AS ClaimDate
FROM MyTable

这导致错误消息：

转换日期和/或字符的时间时转换失败 字符串。

我也尝试了CONVERT(DATE, ClaimDate)，但无济于事。

现在，不仅知道为什么我会遇到这个错误，我还想知道哪些值是为此引起的。

我已经看到了上面错误消息的几个问题。但是我还没有找到一个答案。

当然，建议除了获得不良价值之外提出解决方案。

Answer 1

取决于您的SQL Server版本（2012 ），您可以使用try_convert：

SELECT ClaimDate 
FROM   MyTable
WHERE  TRY_CONVERT(DATE, ClaimDate ) IS NULL
    AND ClaimDate IS NOT NULL

这将为您提供无法转换为日期的值，如何解决这些问题是另一个问题。另外，可以将2019-01-12的值转换为一个值，但是如果它在字符串中，则不能确定这是12月还是1月的日期。您可能会有效但日期错误！

Answer 2

正如其他人所解释的那样，真正的问题是使用varchar而不是date作为类型。这允许将不良数据插入现场。解决此问题可能是不可能的 - 如果使用不同行中的不同日期格式有字符串怎么办？

CAST(ClaimDate AS DATE)如果文本字段包含非日期字符串，则可能会失败。在这种情况下，行可以忽略：

SELECT ClaimDate AS ClaimDate
FROM MyTable
where TRY_CAST(ClaimDate as date) is not null

很幸运的是date。datetime解析受dateFormat设置的影响。如果将其设置为YDM，则此行仍将起作用：

set dateformat ydm;
select cast('2019-03-14' as date);

虽然这会扔

set dateformat ydm;
select cast('2019-03-14' as datetime);

如果值为2019-03-04，情况会更糟。演员们不会投掷，而是返回错误的日期。

幸运的是，从dateformat的言论：

日期，dateTime2和dateTimeOffset数据类型不支持DateFormat YDM。

这就是为什么应首先使用

检查失败数据的原因

SELECT ClaimDate AS ClaimDate
FROM MyTable
where TRY_CAST(ClaimDate as date) is not null

如果一切正常，则应将类型更改为date，以确保不能插入不良值