SQL Server Convert函数转换异常咨询：双OR条件触发报错

在SQL Server 2022及2016版本中运行以下SQL语句时出现异常：

select *
from (
    select t1.name
         , t1.object_id
         , REPLACE(RIGHT(t1.name, 10), '_', '-') drop_date
      from  sys.tables  t1
     where 1=0
      or t1.name like '[_]%[_][0][1-9][_][0-3][0-9][_][0-2][0-9][0-9][0-9]'
      or t1.name like '[_]%[_][1][0-2][_][0-3][0-9][_][0-2][0-9][0-9][0-9]'
    ) t2
where Convert(datetime2, t2.drop_date, 110) <= sysdatetime();

问题现象 #

• 注释掉子查询中任意一个OR条件时，查询可正常执行；
• 同时保留两个OR条件时，触发如下错误：

ODBC Error in TOdbcStatement.DoExecute (SQLState 22007): [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Conversion failed when converting date and/or time from character string. (241)

已知存在其他查询写法，但怀疑这是SQL Server CONVERT函数的bug，特求证：是忽略了某些细节，还是CONVERT函数的表现不符合文档说明？

参考文档说明（官方文档翻译） #

SQL Server的CONVERT函数中，样式110对应的是美国日期格式，格式为mm-dd-yyyy，用于将字符串转换为日期时间类型时，要求输入字符串严格匹配该格式。

问题分析 #

这不是CONVERT函数的bug，而是SQL Server查询优化器的谓词下推行为导致的。SQL Server的优化器会尝试调整执行计划以提升效率，当子查询使用多个OR条件时，优化器可能会选择先执行外层的CONVERT转换，再应用子查询的过滤条件——此时部分不符合mm-dd-yyyy格式的字符串会被传入CONVERT函数，从而触发转换失败的错误。

当只保留一个OR条件时，优化器能够判断出子查询返回的drop_date都符合格式要求，因此会先执行过滤再转换，不会报错。

解决方法 #

可以通过以下方式避免该问题：

1. 使用TRY_CONVERT替代CONVERT：转换失败时返回NULL，不会触发报错

   select *
   from (
       select t1.name
            , t1.object_id
            , REPLACE(RIGHT(t1.name, 10), '_', '-') drop_date
         from  sys.tables  t1
        where 1=0
         or t1.name like '[_]%[_][0][1-9][_][0-3][0-9][_][0-2][0-9][0-9][0-9]'
         or t1.name like '[_]%[_][1][0-2][_][0-3][0-9][_][0-2][0-9][0-9][0-9]'
       ) t2
   where TRY_CONVERT(datetime2, t2.drop_date, 110) <= sysdatetime();
   

2. 将转换逻辑移入子查询：确保只有符合条件的行才会进行转换

   select *
   from (
       select t1.name
            , t1.object_id
            , CONVERT(datetime2, REPLACE(RIGHT(t1.name, 10), '_', '-'), 110) drop_date
         from  sys.tables  t1
        where 1=0
         or t1.name like '[_]%[_][0][1-9][_][0-3][0-9][_][0-2][0-9][0-9][0-9]'
         or t1.name like '[_]%[_][1][0-2][_][0-3][0-9][_][0-2][0-9][0-9][0-9]'
       ) t2
   where t2.drop_date <= sysdatetime();
   

3. 使用CASE语句过滤有效格式：明确只对符合格式的字符串进行转换

   select *
   from (
       select t1.name
            , t1.object_id
            , REPLACE(RIGHT(t1.name, 10), '_', '-') drop_date
         from  sys.tables  t1
        where 1=0
         or t1.name like '[_]%[_][0][1-9][_][0-3][0-9][_][0-2][0-9][0-9][0-9]'
         or t1.name like '[_]%[_][1][0-2][_][0-3][0-9][_][0-2][0-9][0-9][0-9]'
       ) t2
   where CASE WHEN t2.drop_date LIKE '[0-1][0-9]-[0-3][0-9]-[0-9][0-9][0-9][0-9]' 
              THEN Convert(datetime2, t2.drop_date, 110) 
              ELSE NULL END <= sysdatetime();
   

内容的提问来源于stack exchange，提问作者michael.moyer