SQL Server：拆分字段并转换多格式日期为标准YYYY-MM-DD格式

需求：将Report表的Reference字段拆分为Type和Date两个独立字段，同时把手动输入的多种格式日期（如11/8/22、07/22/2022）统一转换为YYYY-MM-DD的标准格式，没有日期的记录Date列显示NULL。

原表结构及测试数据：

CREATE TABLE Report
(Id INT, Reference VARCHAR(30));

INSERT INTO Report
VALUES
(1, 'Location (11/8/22)'),
(2, 'Timesheet (11/10/22)'),
(3, 'TESTING (12/09/22)'),
(4, 'Incorrect Payment (9/10/22)'),
(5, 'Employee (11/9/22)'),
(6, 'Authorization'),
(7, 'Inactive Client'),
(8, 'Active Client (07/22/2022)'),
(9, 'TESTING (09/09/22)'),
(10, 'Timesheet')

SELECT * FROM Report

当前已实现字段拆分，但无法去除日期字符串的括号并完成格式转换，现有代码：

SELECT 

   p.*

   ,SUBSTRING(p.Reference, 1, CASE CHARINDEX('(', p.Reference)
        WHEN 0
            THEN LEN(p.Reference)
        ELSE CHARINDEX('(', p.Reference) - 1
        END) AS Type

   ,SUBSTRING(p.Reference, CASE CHARINDEX('(', p.Reference)
        WHEN 0
            THEN LEN(p.Reference) + 1
        ELSE CHARINDEX('(', p.Reference) + 0
        END, 1000) AS Date

FROM Report AS p

解决方案 #

可以通过REPLACE去除括号，再用TRY_CONVERT函数处理多种日期格式（该函数能自动识别常见的美式日期格式，转换失败时返回NULL，正好适配无日期的场景）。修改后的完整SQL如下：

SELECT
    p.Id,
    p.Reference,
    -- 提取Type部分，保留原有逻辑
    SUBSTRING(p.Reference, 1, CASE CHARINDEX('(', p.Reference)
        WHEN 0 THEN LEN(p.Reference)
        ELSE CHARINDEX('(', p.Reference) - 1
    END) AS Type,
    -- 处理日期：提取括号内内容→去除括号→转换为标准格式
    TRY_CONVERT(date, 
        SUBSTRING(p.Reference, CHARINDEX('(', p.Reference) + 1, 
            CHARINDEX(')', p.Reference) - CHARINDEX('(', p.Reference) - 1
        ), 101) AS Date -- 101代表美式日期格式（mm/dd/yyyy），兼容两位/四位年份
FROM Report AS p

关键说明 #

1. 提取纯日期字符串：通过CHARINDEX定位(和)的位置，用SUBSTRING直接截取中间的日期内容，自动去掉括号。
2. 统一日期格式：TRY_CONVERT(date, 日期字符串, 101)指定按美式日期规则解析，不管输入是mm/dd/yy还是mm/dd/yyyy，都能转换为YYYY-MM-DD的标准格式；如果没有日期或格式无效，自动返回NULL。

执行后即可得到符合要求的数据集，Date列统一为标准格式，无日期的记录显示NULL。

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