在CASE WHEN语句中使用TRY_PARSE遇到的日期验证问题

嘿，针对你要验证多种日期格式（像Thursday March 15, 2018、05-21-1995、04.03.1934这类），还要处理N/A、####这类无效值的需求，我整理了一套能直接用在存储过程里的SQL方案，逻辑清晰还能覆盖你的场景：

核心逻辑 #

• 用TRY_PARSE（SQL Server 2012+支持）搭配en-US区域设置，自动识别美式风格的多种日期格式
• 解析成功的有效日期存入date_column，解析失败的无效值则标记错误状态
• 优化了你原SQL里的冗余写法，让代码更易维护

完整SQL代码 #

INSERT INTO table_name(date_column, date_error)
SELECT
    -- 有效日期转成标准datetime类型，无效则设为NULL（如果要存原字符串，需把date_column改成varchar类型）
    CASE 
        WHEN TRY_PARSE(input_date_str AS DATETIME USING 'en-US') IS NOT NULL 
        THEN TRY_PARSE(input_date_str AS DATETIME USING 'en-US') 
        ELSE NULL 
    END AS date_column,
    -- 错误标记：1表示无效日期，0表示有效
    CASE 
        WHEN TRY_PARSE(input_date_str AS DATETIME USING 'en-US') IS NULL 
        THEN 1 
        ELSE 0 
    END AS date_error
FROM your_source_table; -- 替换成你的数据源表

细节说明 #

• 支持的格式：TRY_PARSE+en-US能自动识别这些格式：
  • 带星期的英文长格式：Thursday March 15, 2018
  • 短横线分隔的美式日期：05-21-1995（MM-DD-YYYY）
  • 点分隔的日期：04.03.1934（默认解析为MM.DD.YYYY，符合美式习惯）
• 无效值处理：N/A、####这类无法解析的字符串会让TRY_PARSE返回NULL，这时我们标记date_error=1，同时date_column设为NULL——如果需要保留原无效字符串，把ELSE NULL改成ELSE input_date_str就行，但要注意date_column得是VARCHAR类型，不能是日期类型
• 性能优化：如果处理超大量数据，TRY_PARSE的性能不如TRY_CONVERT。要是你的日期格式是固定几种，可以用指定格式代码的TRY_CONVERT来提速：

  -- 示例：针对固定格式的TRY_CONVERT写法
  CASE
      WHEN TRY_CONVERT(DATETIME, input_date_str, 101) IS NOT NULL THEN TRY_CONVERT(DATETIME, input_date_str, 101) -- MM/DD/YYYY
      WHEN TRY_CONVERT(DATETIME, input_date_str, 100) IS NOT NULL THEN TRY_CONVERT(DATETIME, input_date_str, 100) -- 带星期的英文格式（比如Thu Mar 15 2018）
      WHEN TRY_CONVERT(DATETIME, input_date_str, 102) IS NOT NULL THEN TRY_CONVERT(DATETIME, input_date_str, 102) -- YYYY.MM.DD（如果你的点分隔是年在前的话用这个）
      ELSE NULL
  END
  

注意点 #

• 确保你的SQL Server版本是2012及以上，不然不支持TRY_PARSE；要是用的老版本，得写自定义函数来做日期验证
• 如果只需要日期部分不需要时间，把DATETIME改成DATE类型就行

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