SQL Server如何从字符串中提取日期并转换为标准date格式

SQL Server 没有提供直接从任意自由文本中抽取日期的专用函数，可通过内置字符串定位、替换函数搭配日期转换函数实现，比固定用LEFT/RIGHT按位数截取兼容性更强，最终可直接输出标准date类型结果，也能直接提取年份。

可直接运行的实现代码 #

SELECT
    -- 转换得到标准date类型日期
    TRY_CONVERT(
        date,
        -- 移除日期中日的序数后缀（st/nd/rd/th）
        STUFF(
            -- 从第一个数字出现的位置开始截取到字符串末尾，兼容前置文本长度变化的场景
            SUBSTRING(
                'I was born on 1st Jan 1994',
                PATINDEX('%[0-9]%', 'I was born on 1st Jan 1994'),
                LEN('I was born on 1st Jan 1994')
            ),
            2, -- 从日数字的下一位开始替换
            2, -- 替换2位字符，刚好覆盖st/nd/rd/th这类后缀
            ''
        ),
        106 -- 对应SQL Server中dd mon yyyy（日月英文缩写年）的日期格式编码
    ) AS standard_birth_date,
    -- 直接从转换后的日期中提取年份
    YEAR(
        TRY_CONVERT(
            date,
            STUFF(
                SUBSTRING(
                    'I was born on 1st Jan 1994',
                    PATINDEX('%[0-9]%', 'I was born on 1st Jan 1994'),
                    LEN('I was born on 1st Jan 1994')
                ),
                2,2,''
            ),
            106
        )
    ) AS birth_year

返回结果说明 #

• standard_birth_date为标准date类型，返回值：1994-01-01
• birth_year为整数类型，返回值：1994

实现逻辑说明 #

• 用PATINDEX('%[0-9]%', 原字符串)自动定位日期段的起始位置，替代固定长度截取的写法，就算前置文本长度变化（比如把on改成in）也不会截错内容
• 序数后缀（1后面的st、2后面的nd这类）会导致日期转换失败，用STUFF函数精准移除后再做转换
• 用TRY_CONVERT做类型转换，遇到格式异常的字符串不会直接抛出报错，会返回NULL，方便排查脏数据

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