SQL Server中NVARCHAR转DATETIME部分值异常为NULL求助

需要将SQL Server临时表#TempIntermediateResults的TempExpirationDate列从NVARCHAR类型转换为DATETIME类型，该列数据来自网站爬取，包含多种格式的日期字符串，必须先清洗再转换成标准格式。

1. 先执行清理SQL，移除特殊字符和冗余空格：

UPDATE #TempIntermediateResults
SET TempExpirationDate =
    CASE
        -- 若日期为'2013-09-23 00:00:00'格式则不处理
        WHEN TempExpirationDate LIKE '%[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]%'
            THEN TempExpirationDate
        -- 空值保持不变
        WHEN TempExpirationDate IS NULL
            THEN NULL
        -- 处理'February 2016)'这类带右括号的年月格式
        WHEN CHARINDEX(')', TempExpirationDate) > 0
            THEN LTRIM(RTRIM(REPLACE(SUBSTRING(TempExpirationDate, 1, CHARINDEX(')', TempExpirationDate)), ')', '')))
        -- 处理'May 8, 2019 ('或'May 5, 2015),'这类带左括号的格式
        WHEN CHARINDEX('(', TempExpirationDate) > 0
            THEN LTRIM(RTRIM(REPLACE(SUBSTRING(TempExpirationDate, 1, CHARINDEX('(', TempExpirationDate)), '(', '')))
        -- 移除其他特殊字符（包括点号）并去除首尾空格
        ELSE LTRIM(RTRIM(REPLACE(REPLACE(TempExpirationDate, SUBSTRING(TempExpirationDate, PATINDEX('%[^a-zA-Z0-9 ]%', TempExpirationDate + '0'), 1), ''), '.', '')))
    END;

清理后部分值如"July 4 2018 "看起来格式正常。

2. 再执行转换SQL尝试转为DATETIME，设置默认值'2000-12-31T00:00:00'用于排查问题：

-- 更新临时表
UPDATE #TempIntermediateResults
SET TempExpirationDate =
    CASE
        -- 先尝试直接转换
        WHEN TRY_CAST(TempExpirationDate AS DATETIME) IS NOT NULL
            THEN TRY_CAST(TempExpirationDate AS DATETIME)
        -- 处理带逗号的格式
        WHEN CHARINDEX(',', TempExpirationDate) > 0
            THEN TRY_CAST(REPLACE(TempExpirationDate, ',', '') AS DATETIME)
        -- 处理残留右括号的情况
        WHEN CHARINDEX(')', TempExpirationDate) > 0
            THEN TRY_CAST(REPLACE(SUBSTRING(TempExpirationDate, 1, CHARINDEX(')', TempExpirationDate)), ')', '') AS DATETIME)
        -- 处理带空格的情况（尝试去掉所有空格后转换）
        WHEN CHARINDEX(' ', LTRIM(RTRIM(TempExpirationDate))) > 0
            THEN TRY_CAST(REPLACE(LTRIM(RTRIM(TempExpirationDate)), ' ', '') AS DATETIME)
        ELSE '2000-12-31T00:00:00'   -- 所有条件不匹配时的默认测试值，用于排查
    END;

大部分日期值成功转换，但像"July 4 2018 "、"March 7 2019 "这类清理后的字符串，转换后结果为NULL，且没有触发默认值'2000-12-31T00:00:00'，需要找出原因和解决办法。

原始样本数据：

2013-09-23 00:00:00
NULL
July 2  2022
NULL
May 5, 2015),
January 25, 2018.
March 7, 2019  
January 8, 2019  
September 8, 2019  
April 5  2021
January 8  2021
May 8, 2019 (
April 06  2023
January 14, 2023
July 15, 2022
July 4, 2018  
February 2016)  

1. TRY_CAST的语言环境限制：SQL Server的TRY_CAST依赖当前会话的语言设置，如果服务器默认语言不是英语，无法识别英文月份名称（比如"July"、"March"），导致转换失败返回NULL。
2. 转换逻辑的顺序问题：CASE语句中，CHARINDEX(' ', ...) > 0的条件会被触发，但执行REPLACE(..., ' ', '')后得到"July42018"这类完全无法转换的格式，TRY_CAST仍返回NULL，最终整个CASE表达式返回NULL，不会走到ELSE分支（因为前面的条件已经匹配，只是转换结果无效）。

方法1：使用TRY_PARSE（推荐，SQL Server 2012及以上版本） #

TRY_PARSE支持解析英文日期格式，可指定文化参数，不受会话语言影响：

UPDATE #TempIntermediateResults
SET TempExpirationDate =
    CASE
        -- 先处理标准ISO格式
        WHEN TempExpirationDate LIKE '%[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]%'
            THEN TRY_CAST(TempExpirationDate AS DATETIME)
        -- 空值保持不变
        WHEN TempExpirationDate IS NULL
            THEN NULL
        -- 尝试用英文文化解析日期
        WHEN TRY_PARSE(TempExpirationDate AS DATETIME USING 'en-US') IS NOT NULL
            THEN TRY_PARSE(TempExpirationDate AS DATETIME USING 'en-US')
        -- 处理残留的特殊字符后再解析
        ELSE TRY_PARSE(
                LTRIM(RTRIM(REPLACE(REPLACE(TempExpirationDate, '(', ''), ')', ''))) 
                AS DATETIME USING 'en-US'
             )
    END;
-- 最后处理所有无法转换的值，设置默认值
UPDATE #TempIntermediateResults
SET TempExpirationDate = '2000-12-31T00:00:00'
WHERE TRY_CAST(TempExpirationDate AS DATETIME) IS NULL;

方法2：临时修改会话语言 #

在转换前设置会话语言为英语，让TRY_CAST能识别英文月份：

-- 设置会话语言为英语
SET LANGUAGE English;

UPDATE #TempIntermediateResults
SET TempExpirationDate =
    CASE
        WHEN TRY_CAST(TempExpirationDate AS DATETIME) IS NOT NULL
            THEN TRY_CAST(TempExpirationDate AS DATETIME)
        WHEN CHARINDEX(',', TempExpirationDate) > 0
            THEN TRY_CAST(REPLACE(TempExpirationDate, ',', '') AS DATETIME)
        ELSE '2000-12-31T00:00:00'
    END;

-- 恢复原语言（可选，根据实际情况）
SET LANGUAGE 简体中文; -- 替换为你的原语言

方法3：拆分日期组件手动转换 #

如果无法使用TRY_PARSE，可以手动拆分月份、日、年，映射月份名称为数字后用DATEFROMPARTS生成日期：

UPDATE #TempIntermediateResults
SET TempExpirationDate =
    CASE
        WHEN TempExpirationDate IS NULL THEN NULL
        WHEN TempExpirationDate LIKE '%[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]%' THEN TRY_CAST(TempExpirationDate AS DATETIME)
        ELSE
            DATEFROMPARTS(
                -- 提取年份
                CAST(RIGHT(LTRIM(RTRIM(TempExpirationDate)), 4) AS INT),
                -- 映射月份名称为数字
                CASE LEFT(LTRIM(RTRIM(TempExpirationDate)), CHARINDEX(' ', TempExpirationDate)-1)
                    WHEN 'January' THEN 1
                    WHEN 'February' THEN 2
                    WHEN 'March' THEN 3
                    WHEN 'April' THEN 4
                    WHEN 'May' THEN 5
                    WHEN 'June' THEN 6
                    WHEN 'July' THEN 7
                    WHEN 'August' THEN 8
                    WHEN 'September' THEN 9
                    WHEN 'October' THEN 10
                    WHEN 'November' THEN 11
                    WHEN 'December' THEN 12
                    ELSE 1 -- 默认值，可根据需求调整
                END,
                -- 提取日期（处理无日期的情况，比如只有年月的格式）
                CASE 
                    WHEN CHARINDEX(' ', LTRIM(RTRIM(TempExpirationDate)), CHARINDEX(' ', LTRIM(RTRIM(TempExpirationDate)))+1) > 0
                    THEN CAST(SUBSTRING(LTRIM(RTRIM(TempExpirationDate)), CHARINDEX(' ', TempExpirationDate)+1, CHARINDEX(' ', TempExpirationDate, CHARINDEX(' ', TempExpirationDate)+1) - CHARINDEX(' ', TempExpirationDate)-1) AS INT)
                    ELSE 1 -- 无日期时默认取当月第一天
                END
            )
    END;

-- 设置无法转换的默认值
UPDATE #TempIntermediateResults
SET TempExpirationDate = '2000-12-31T00:00:00'
WHERE TRY_CAST(TempExpirationDate AS DATETIME) IS NULL;

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