将VARCHAR存储的SMALLDATETIME转换为DATE失败求助

一、先定位异常数据 #

样本数据格式正常不代表全表数据都合规，先找出导致转换失败的异常记录：

1. 筛选无法转换为DATE的记录：

SELECT Hit_DateTime
FROM 你的源表名
WHERE TRY_CAST(SUBSTRING(Hit_DateTime, 1, 10) AS DATE) IS NULL
AND Hit_DateTime IS NOT NULL;

2. 检查字符串长度是否异常（正常格式yyyy-MM-dd HH:mm:ss总长度为19）：

SELECT Hit_DateTime, LEN(Hit_DateTime) AS 字符串长度
FROM 你的源表名
WHERE LEN(Hit_DateTime) != 19;

3. 排查隐藏特殊字符（如空格、制表符、非数字/符号字符）：

SELECT Hit_DateTime
FROM 你的源表名
WHERE Hit_DateTime LIKE '%[^0-9 :-]%';

二、优化转换逻辑 #

不用截取字符串，直接转换完整的时间字符串更可靠，SQL Server会自动提取日期部分：

-- 直接转换为DATE类型
SELECT CAST(Hit_DateTime AS DATE) AS Hit_Date
FROM 你的源表名;

-- 用TRY_CAST避免报错，同时自动过滤无效数据
SELECT TRY_CAST(Hit_DateTime AS DATE) AS Hit_Date
FROM 你的源表名
WHERE TRY_CAST(Hit_DateTime AS DATE) IS NOT NULL;

原方法报错的原因 #

• SQL Server中SUBSTRING的起始索引是1，你用0虽然会被自动修正为1，但如果部分记录开头有隐藏空格，截取的内容会包含空格，导致转换失败；
• 若存在日期部分长度不足10位的异常记录，截取后的字符串无法被识别为合法日期。

三、批量插入的正确写法 #

如果要插入到目标表，推荐用TRY_CAST过滤无效数据后插入：

INSERT INTO 你的目标表名(目标日期列名)
SELECT TRY_CAST(Hit_DateTime AS DATE)
FROM 你的源表名
WHERE TRY_CAST(Hit_DateTime AS DATE) IS NOT NULL;

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