使用isdate()筛选nvarchar(max)列日期时遇字符串截断错误求解决

问题原因 #

1. ISDATE返回值类型误用：你在WHERE子句和CASE表达式中用字符串'1'和ISDATE的返回值比较，但ISDATE实际返回的是整数1/0，隐式类型转换可能引发意外的执行逻辑。
2. 查询优化器执行顺序问题：SQL Server的查询优化器可能调整执行顺序，先执行SELECT中的转换操作，再应用WHERE过滤，导致不符合日期格式的行被强制转换，触发截断错误。

解决方案 #

方案一：用CTE先过滤有效行再转换

先通过CTE筛选出符合日期格式的清理后的数据，再进行转换，避免无效数据进入转换步骤：

WITH ValidDateComments AS (
    SELECT LTRIM(RTRIM(sComment)) AS CleanComment
    FROM Activity
    WHERE ISDATE(LTRIM(RTRIM(sComment))) = 1
)
SELECT CAST(CONVERT(DATETIME, CleanComment, 101) AS DATE) AS sComment
FROM ValidDateComments

方案二：使用TRY_CONVERT替代ISDATE

TRY_CONVERT在转换失败时返回NULL，不会触发错误，同时WHERE子句过滤掉转换失败的行，从根源避免报错：

SELECT CAST(TRY_CONVERT(DATETIME, LTRIM(RTRIM(sComment)), 101) AS DATE) AS sComment
FROM Activity
WHERE TRY_CONVERT(DATETIME, LTRIM(RTRIM(sComment)), 101) IS NOT NULL

说明 #

• 先执行LTRIM(RTRIM())清理字符串前后空格，避免空格导致的ISDATE判断失误。
• 针对你的样本数据，前两个标准日期字符串会被正确转换，其余无效数据会被过滤，不会触发错误。

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