Microsoft SQL插入时转换字符串'03-January-2023'为datetime失败求助

核心排查方向 #

错误提示的转换失败并非来自硬编码的'03-January-2023'（该格式SQL Server可正常识别），大概率是源表CUSTOMERS_INDIA中的[PURCHASE TIME]列存在格式不规范的字符串，无法自动转换为目标表CUSTOMERS_PURCHASE的[PURCHASE_TIME]字段类型。

第一步：定位异常数据 #

先查询源表中无法转换的无效时间字符串：

SELECT [PURCHASE TIME]
FROM CUSTOMERS_INDIA
WHERE TRY_CAST([PURCHASE TIME] AS DATETIME2) IS NULL

这条语句会筛选出所有不符合DATETIME2格式的数据，比如空字符串、25:00这类非法时间、与服务器默认格式不匹配的日期字符串等。

针对性解决方法 #

1. 修复异常数据 #

对查出的无效值进行修正：补全合法时间、删除无意义数据，或统一调整为标准格式（如HH:mm:ss、YYYY-MM-DD HH:mm:ss）。

2. 显式指定转换格式 #

如果[PURCHASE TIME]列有固定格式，用CONVERT函数指定格式代码，避免依赖服务器默认日期设置：

INSERT INTO CUSTOMERS_PURCHASE([PURCHASE_DATE], [PURCHASE_TIME], [PURCHASE_CITY]) 
SELECT 
    CAST('03-January-2023' AS DATETIME2) AS PURCHASE_DATE,  
    CONVERT(DATETIME2, [PURCHASE TIME], 108) AS PURCHASED_TIME, -- 108对应HH:mm:ss格式
    [PURCHASE_CITY] 
FROM CUSTOMERS_INDIA

常用格式代码参考：

• 101：MM/DD/YYYY
• 103：DD/MM/YYYY
• 120：YYYY-MM-DD HH:mi:ss

3. 跳过错误数据（临时方案） #

如果暂时无法修复异常数据，用TRY_CAST替代CAST，转换失败的行返回NULL，避免整个插入语句中断：

INSERT INTO CUSTOMERS_PURCHASE([PURCHASE_DATE], [PURCHASE_TIME], [PURCHASE_CITY]) 
SELECT 
    CAST('03-January-2023' AS DATETIME2) AS PURCHASE_DATE,  
    TRY_CAST([PURCHASE TIME] AS DATETIME2) AS PURCHASED_TIME,
    [PURCHASE_CITY] 
FROM CUSTOMERS_INDIA
-- 可选：仅插入转换成功的数据
WHERE TRY_CAST([PURCHASE TIME] AS DATETIME2) IS NOT NULL

额外检查 #

确认目标表CUSTOMERS_PURCHASE的[PURCHASE_TIME]字段类型为DATETIME2（或兼容的日期时间类型），如果是DATE类型但源数据包含时间部分，也会导致转换失败。

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