SQL Server：如何返回日期转换失败的对应行数据？

针对SQL Server 2012及以上版本 #

直接使用TRY_CONVERT函数，它在转换失败时不会抛出错误，而是返回NULL，借此可以筛选出转换失败的记录：

SELECT 
    主键ID字段名,
    field
FROM 
    你的表名
WHERE 
    TRY_CONVERT(datetime, field + '/2006', 103) IS NULL

替换语句中的主键ID字段名和你的表名为实际名称即可执行，结果会返回所有无法转换的field值及其对应主键ID。

针对SQL Server 2012之前的旧版本 #

由于旧版本没有TRY_CONVERT，可以通过自定义函数结合错误捕获来判断日期有效性：

1. 创建日期验证函数 #

CREATE FUNCTION dbo.IsValidDate(@DateStr VARCHAR(10))
RETURNS BIT
AS
BEGIN
    DECLARE @IsValid BIT = 0
    BEGIN TRY
        -- 尝试转换，成功则标记为有效
        DECLARE @DummyDate DATETIME = CONVERT(DATETIME, @DateStr, 103)
        SET @IsValid = 1
    END TRY
    BEGIN CATCH
        -- 转换失败则标记为无效
        SET @IsValid = 0
    END CATCH
    RETURN @IsValid
END

2. 查询问题行 #

SELECT 
    主键ID字段名,
    field
FROM 
    你的表名
WHERE 
    dbo.IsValidDate(field + '/2006') = 0

额外排查提示 #

找到问题行后，可以检查字段值是否包含：

• 空格、制表符等不可见字符
• 字母、特殊符号等非数字/斜杠字符
• 不符合dd/mm格式的内容（比如单数字日/月、多余分隔符）

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