将混合格式VARCHAR转换为DATETIME时出现超范围值错误求助

错误原因 #

直接使用CAST转换混合格式的varchar字符串到DATETIME时，SQL Server无法同时兼容两种差异明显的日期格式；此外，数据中可能存在无效日期值（比如30/02/2023这类不存在的日期），导致转换时触发范围溢出错误。

解决方案 #

1. 先定位异常行

先筛选出所有无法正常转换的started_at值，排查问题数据：

SELECT started_at
FROM [dbo].[testCyclistic12m]
WHERE TRY_CONVERT(DATETIME, started_at) IS NULL

TRY_CONVERT在转换失败时返回NULL，能快速定位格式不兼容或无效的日期行。

2. 针对两种格式定向转换

利用CASE结合TRY_CONVERT，分别处理两种日期格式：

SELECT 
    CASE
        -- 处理dd/mm/yyyy hh:mm格式（样式103）
        WHEN TRY_CONVERT(DATETIME, started_at, 103) IS NOT NULL 
            THEN CONVERT(DATETIME, started_at, 103)
        -- 处理yyyy-mm-dd hh:mm:ss格式（样式120）
        WHEN TRY_CONVERT(DATETIME, started_at, 120) IS NOT NULL
            THEN CONVERT(DATETIME, started_at, 120)
        -- 转换失败时返回NULL，可根据需求替换为默认值
        ELSE NULL
    END AS converted_started_at
FROM [dbo].[testCyclistic12m]

如果需要输出统一的dd/mm/yyyy格式字符串，可嵌套一层CONVERT：

SELECT 
    CONVERT(VARCHAR(20),
        CASE
            WHEN TRY_CONVERT(DATETIME, started_at, 103) IS NOT NULL 
                THEN CONVERT(DATETIME, started_at, 103)
            WHEN TRY_CONVERT(DATETIME, started_at, 120) IS NOT NULL
                THEN CONVERT(DATETIME, started_at, 120)
            ELSE NULL
        END, 103) AS formatted_started_at
FROM [dbo].[testCyclistic12m]

3. 持久化转换结果（可选）

若需将转换后的数据存入表中，可新增DATETIME列并批量更新：

-- 新增日期列
ALTER TABLE [dbo].[testCyclistic12m] ADD started_at_datetime DATETIME

-- 批量更新数据
UPDATE [dbo].[testCyclistic12m]
SET started_at_datetime = 
    CASE
        WHEN TRY_CONVERT(DATETIME, started_at, 103) IS NOT NULL 
            THEN CONVERT(DATETIME, started_at, 103)
        WHEN TRY_CONVERT(DATETIME, started_at, 120) IS NOT NULL
            THEN CONVERT(DATETIME, started_at, 120)
        ELSE NULL
    END

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