T-sql - 将 nvarchar 数据类型转换为 datetime 数据类型导致值超出范围。 = 和 LIKE 之间的区别

Question

我有一个带有以下 WHERE 子句的查询

WHERE
    I.new_outstandingamount = 70
    AND ISNUMERIC(SUBSTRING(RA.new_stampernumber,7, 4)) = 1
    AND (DATEDIFF(M,T.new_commencementdate, SUBSTRING(RA.new_stampernumber,7, 10)) >= 1)
    AND RA.new_applicationstatusname = 'Complete'
    AND I.new_feereceived > 0
    AND RA.new_stampernumber IS NOT NULL
    AND T.new_commencementdate IS NOT NULL

RA.new_stampernumber 是一个字符串值，其中包含三个统一长度的串联信息。该字符串中的中间信息是格式为 yyyy-MM-dd 的日期。

为了过滤掉该字符串中的日期格式不符合预期的任何行，我使用 ISNUMERIC 函数检查前 4 个字符是否为数字。

当我运行查询时，我收到一条错误消息，指出

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

导致此错误发生的行是

AND (DATEDIFF(M,T.new_commencementdate, SUBSTRING(RA.new_stampernumber,7, 10)) >= 1)

当我注释掉此行时，我没有收到错误。

奇怪的是，如果我替换

AND ISNUMERIC(SUBSTRING(RA.new_stampernumber,7, 4)) = 1

为

AND SUBSTRING(RA.new_stampernumber,7, 4) IN ('2003','2004','2005','2006','2007','2008','2009','2010', '2011', '2012','2013','2014','2015'))

查询运行成功。

更奇怪的是，如果我用这个替换上面的工作行，

AND SUBSTRING(RA.new_stampernumber,11, 1) = '-'

我会再次收到错误消息。但是，如果我用 LIKE 比较替换等号，它就会起作用：

AND SUBSTRING(RA.new_stampernumber,11, 1) LIKE '-'

当我删除 DATEDIFF 函数并比较每个查询的结果时，它们都返回相同的结果集，因此它不是由不同子句返回的不同数据引起的。

如果每个子句返回的数据实际上是相同的，谁能向我解释什么可能导致某些子句抛出超出范围的错误，而不是其他子句？

谢谢， 尼尔

Answer 1

不同的执行计划。

不保证 WHERE 子句按特定顺序处理。据推测，当它工作时，它会在尝试强制转换之前过滤掉错误行。

另外 ISNUMERIC 本身对于您想要的东西来说并不是很可靠。我会将 DATEDIFF 表达式更改为如下所示

DATEDIFF(M, T.new_commencementdate, 
                    CASE
                    WHEN RA.new_stampernumber LIKE 
                    '______[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]%' 
                     THEN SUBSTRING(RA.new_stampernumber, 7, 10)
                     END) >= 1 )