MS SQL 2016中Varchar日期转单日期的错误识别优化问询

问题背景 #

现有一张表，包含Month（Varchar）、Day（Varchar）、Year（Varchar）三个字段，需要将这三个字段的日期信息合并格式化为单个日期类型字段。目前使用CASE语句过滤错误日期，现咨询两个问题：

1. 是否有更高效的写法替代现有的CASE判断？
2. 在MS SQL 2016中，是否存在可以在格式化日期前识别并排除错误数据的函数？

表结构示例 #

+------------+-----------------+--------------+----------------+
| Date Type  | Month (Varchar) | Day(Varchar) | Year (Varchar) |
+------------+-----------------+--------------+----------------+
| Invalid    |              02 |           31 |           2022 |
| Invalid    |              04 |           31 |           2021 |
| Invalid    |              02 |           29 |           2022 |
| Good Date  |              03 |           22 |           2022 |
+------------+-----------------+--------------+----------------+

现有实现代码 #

1. 标记错误日期

,Case when ( (s.[Day]='31' and s.[Month]='02') 
Or (s.[Day]='30' and s.[Month]='02') or (s.[Day]='29' and s.[Month]='02' and s.[year]='2016') 
 or (s.[Day]='29' and s.[Month]='02' and s.[year]='2019') 
 or (s.[Day]='29' and s.[Month]='02' and s.[year]='2021') 
 or (s.[Day]='29' and s.[Month]='02' and s.[year]='2022') 
 or (s.[Day]='29' and s.[Month]='02' and s.[year]='2023') 
or (s.[Day]='29' and s.[Month]='02' and s.[year]='2025') 
 or (s.[Day]='29' and s.[Month]='02' and s.[year]='2026') 
 Or (s.[Day]='31' and s.[Month]='06')  Or (s.[Day]='31' and s.[Month]='04')
 Or (s.[Day]='31' and s.[Month]='09')   Or (s.[Day]='31' and s.[Month]='11') OR (s.[Day] IS null Or s.[Month] IS null Or s.[Year] IS null ) )
   
   then 1 else 0 end as [Wrong Date Entry]

2. 转换日期

CASE when [Wrong Date Entry]=0 
THEN CONVERT(DATE,CAST([Year] AS VARCHAR(4))+'-'+
                    CAST([month] AS VARCHAR(2))+'-'+
                    CAST([day]AS VARCHAR(2))) 
ELSE NULL END AS [Date Type single date]

解决方案 #

问题1：更高效的错误日期判断写法 #

你当前的CASE语句需要枚举大量无效日期场景，不仅冗余还容易遗漏情况。可以利用SQL原生的日期转换特性，结合TRY_CONVERT函数（MS SQL 2012及以上支持）简化判断：

简化错误日期标记

,CASE 
    WHEN TRY_CONVERT(DATE, s.[Year] + '-' + s.[Month] + '-' + s.[Day]) IS NULL THEN 1
    ELSE 0 
END AS [Wrong Date Entry]

简化日期转换

TRY_CONVERT(DATE, s.[Year] + '-' + s.[Month] + '-' + s.[Day]) AS [Date Type single date]

TRY_CONVERT会尝试将拼接后的字符串转换为DATE类型，转换失败（比如无效日期）则返回NULL，无需依赖之前的标记字段，一步完成转换和错误过滤。

这种写法的优势：

• 无需手动枚举所有无效日期场景（如不同年份的2月29日、小月的31日等），自动覆盖所有日期合法性判断
• 代码更简洁，维护成本更低
• 性能和原CASE语句相当，甚至更优——SQL引擎对日期转换的处理是原生优化过的

问题2：MS SQL 2016中的错误识别函数 #

MS SQL 2016支持TRY_CONVERT和TRY_CAST两个函数，专门用于转换数据类型时捕获错误，避免无效数据导致查询报错：

• TRY_CONVERT(DATE, 日期字符串)：尝试将字符串转为DATE类型，失败返回NULL
• TRY_CAST(日期字符串 AS DATE)：功能和TRY_CONVERT一致，仅语法不同

这两个函数能自动识别并排除以下错误数据：

• 日期格式无效（如月份>12、日期>当月最大天数）
• 闰年/平年的2月29日判断错误
• 任意字段为NULL的情况

如果需要更细粒度的错误原因识别，可结合ERROR_NUMBER()等函数，但对于你的场景，TRY_CONVERT已经完全满足需求。

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