如何在SQL Server中从复杂格式文本字段提取日期时间数据

解决方案 #

你可以通过截取+清理后缀+格式转换三步实现，无需手动拆分每个日期部分，SQL Server 2008及以上版本都支持：

完整实现代码 #

WITH sample_data AS (
    SELECT 'some text - 29th Jul 2021 16:44' AS textfield
    UNION
    SELECT 'some different text - 2nd Jul 2021 12:31' AS textfield
)
SELECT 
    textfield,
    TRY_CONVERT(DATETIME, 
        STUFF(
            LTRIM(SUBSTRING(textfield, CHARINDEX('-', textfield) + 1, LEN(textfield))),
            PATINDEX('%[^0-9]%', LTRIM(SUBSTRING(textfield, CHARINDEX('-', textfield) + 1, LEN(textfield)))),
            2,
            ''
        ),
        106
    ) AS extracted_datetime
FROM sample_data

逻辑说明 #

1. 截取日期部分：用CHARINDEX('-', textfield)定位短横线位置，通过SUBSTRING+LTRIM拿到短横线后无前置空格的原始日期字符串，类似29th Jul 2021 16:44。
2. 清理序数后缀：用PATINDEX('%[^0-9]%', 日期字符串)匹配到日部分之后第一个非数字的位置（也就是th/nd/st/rd后缀的起始位），通过STUFF删除该位置开始的2个字符，得到无后缀的合法日期字符串29 Jul 2021 16:44。
3. 转换为日期类型：用TRY_CONVERT搭配格式码106（对应英文日月年格式dd mon yyyy）直接转换为DATETIME类型，转换失败时会返回NULL不会抛出异常，得到的结果可以直接和其他DATETIME字段做比较。

兼容说明 #

该方案对1-9日的单数字日期、双数字日期、月份全称/缩写都可以正常识别，不需要额外做适配。

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