SQL中varchar存储的日期字段校验合法后筛选日期间隔报错如何解决

你遇到的报错是因为SQL Server的查询优化器会自主调整WHERE子句中条件的执行顺序，并不会严格按照你写的「先判断ISDATE再执行CONVERT」的顺序运行。如果优化器优先执行了CONVERT(datetime,t.txt_date)，遇到不符合规范的字符串就会直接抛出转换失败的错误。

可以在同一个SQL脚本中实现先校验再筛选的逻辑，根据你使用的SQL Server版本不同，有两种常用实现方式：

方案1：SQL Server 2012及以上版本（推荐） #

使用TRY_CONVERT函数，这个函数在转换失败时会返回NULL，不会抛出错误，NULL和日期比较会自动被WHERE条件过滤，不需要额外加ISDATE判断：

SELECT DISTINCT t.id, TRY_CONVERT(datetime, t.txt_date) as [date]
FROM [table] t
WHERE TRY_CONVERT(datetime, t.txt_date) BETWEEN @BeginDate AND @EndDate

如果你的日期字符串有固定格式，建议指定转换格式码，比如yyyy-MM-dd格式对应格式码23，写为TRY_CONVERT(datetime, t.txt_date, 23)，避免服务器默认日期配置不同导致的异常转换。

方案2：SQL Server 2008R2及更低版本 #

如果你的数据库版本不支持TRY_CONVERT，可以借助CASE WHEN的执行顺序特性（CASE的判断分支会严格按书写顺序执行）来规避错误：

SELECT DISTINCT t.id, CONVERT(datetime, t.txt_date) as [date]
FROM [table] t
WHERE CASE 
        WHEN ISDATE(t.txt_date) = 1 THEN CONVERT(datetime, t.txt_date) 
      END BETWEEN @BeginDate AND @EndDate

当ISDATE(t.txt_date)不等于1时，CASE表达式返回NULL，比较不成立会自动过滤非法日期的行，同时保证只有合法日期才会执行转换操作。

• 示例中的table是SQL关键字，实际使用时建议加方括号[]转义，或者替换为你的实际表名。
• 如果需要排除结束日期当天的非零点数据，可以把条件调整为TRY_CONVERT(datetime, t.txt_date) >= @BeginDate AND TRY_CONVERT(datetime, t.txt_date) < DATEADD(day, 1, @EndDate)，比BETWEEN的边界判断更准确。

内容的提问来源于stack exchange，提问作者Peter Sun