VARCHAR转DATE类型转换失败求助：CAST报错但TRY_CAST可运行

1. 排查隐形字符问题 #

看起来格式正常的字符串，可能隐藏不可见字符（如空格、制表符、全角空格、换行符），可通过以下方式验证：

• 对比DATALENGTH()和LEN()的返回值：LEN()会忽略末尾空格，DATALENGTH()返回实际字节数，若两者不一致，说明存在隐形字符。
• 清理隐形字符后测试转换：

  SELECT TRY_CAST(REPLACE(REPLACE(your_date_column, CHAR(9), ''), CHAR(32), '') AS DATETIME2)
  FROM your_cte
  

• 逐个字符检查ASCII值，定位异常字符：

  SELECT 
    your_date_column,
    SUBSTRING(your_date_column, n, 1) AS single_char,
    ASCII(SUBSTRING(your_date_column, n, 1)) AS ascii_code
  FROM your_cte
  CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25)) AS nums(n)
  WHERE n <= LEN(your_date_column)
  

2. 修正TRY_CAST的执行顺序问题 #

如果CTE中包含WHERE过滤条件，SQL可能优先执行过滤逻辑，此时TRY_CAST尚未完成转换，导致原字符串直接参与判断触发报错。例如错误写法：

WITH cte AS (
  SELECT TRY_CAST(your_date_column AS DATETIME2) AS converted_date
  FROM your_table
  WHERE converted_date >= '2019-01-01' -- 这里会直接用原字符字段判断，而非转换后的值
)
SELECT * FROM cte

解决方法：将过滤逻辑移到外层查询，确保TRY_CAST先执行：

WITH cte AS (
  SELECT TRY_CAST(your_date_column AS DATETIME2) AS converted_date, your_date_column
  FROM your_table
)
SELECT * FROM cte
WHERE converted_date >= '2019-01-01'

3. 放弃依赖ISDATE()函数 #

ISDATE()对DATETIME2格式的支持存在局限性，比如你提到的7位小数格式2019-06-01 00:00:00.0000000，ISDATE()无法识别，因此全返回0。直接使用TRY_CAST或TRY_CONVERT作为判断标准更可靠。

4. 显式指定转换格式 #

数据库的语言或日期格式设置可能影响转换结果，可显式指定格式代码避免冲突：

SELECT TRY_CONVERT(DATETIME2, your_date_column, 121) -- 121对应ODBC规范的yyyy-mm-dd hh:mi:ss.mmmmmm格式
FROM your_cte

5. 直接定位异常记录 #

用以下语句找出所有无法转换的记录，针对性分析：

SELECT your_date_column
FROM your_table
WHERE TRY_CAST(your_date_column AS DATETIME2) IS NULL

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