SQL中从日期时间区间文本列提取起始时间的适配问题

问题背景 #

我有一个格式为<2/23/23 9:00 am - 2/23/23 9:59 am>或<2/23/2023 9:00 am - 2/23/2023 9:59 am>的文本列，需要用SELECT语句提取起始日期和起始时间：

• 现有起始日期逻辑：LTRIM(RTRIM(CONVERT(DATE,RTRIM(LTRIM(LEFT([Date], CHARINDEX(' ',[Date]) + 0))))))，该逻辑正常工作
• 现有起始时间逻辑：RTRIM(LTRIM(FORMAT(CAST(REPLACE(REPLACE(RTRIM(LTRIM(RIGHT(RIGHT(LEFT([Date], CHARINDEX('-', [Date]) - 1), LEN(LEFT([Date], CHARINDEX('-', [Date]) - 1)) - PATINDEX( '%/[12][0-9] %',LEFT([Date], CHARINDEX('-', [Date]) - 1))),LEN(RIGHT(LEFT([Date], CHARINDEX('-', [Date]) - 1), LEN(LEFT([Date], CHARINDEX('-', [Date]) - 1)) - PATINDEX( '%/[12][0-9] %',LEFT([Date], CHARINDEX('-', [Date]) - 1)))) - 1))),'am','AM'),'pm','PM') AS datetime),'hh:mm tt')))

期望起始时间输出为9:00 am，但当年份为4位格式（如/2023）时，起始时间提取出现问题；尝试修改PATINDEX为%/%[12][0-9] %后，出现“Conversion failed when converting date and/or time from character string.”错误。

解决方案：简化逻辑，兼容两种年份格式 #

原来的起始时间提取逻辑过度依赖年份位数的正则匹配，容易出错。可以换一种思路：先拆分出-左侧的完整起始日期时间字符串，再通过日期与时间之间的空格分隔符提取时间部分，完全不依赖年份位数。

直接SELECT语句写法 #

SELECT
    -- 起始日期（保留原逻辑，已验证正常）
    LTRIM(RTRIM(CONVERT(DATE, LTRIM(RTRIM(LEFT([Date], CHARINDEX(' ', [Date]))))))) AS StartDate,
    -- 兼容两位/四位年份的起始时间
    LTRIM(RTRIM(REPLACE(FORMAT(
        CAST(RTRIM(RTRIM(RIGHT(LEFT([Date], CHARINDEX('-', [Date]) - 1), LEN(LEFT([Date], CHARINDEX('-', [Date]) - 1)) - CHARINDEX(' ', LEFT([Date], CHARINDEX('-', [Date]) - 1))))) AS DATETIME),
        'hh:mm tt'
    ), 'AM', 'am'))) AS StartTime
FROM YourTable

可读性更强的CTE写法 #

如果需要更清晰的逻辑拆分，可使用CTE先提取起始段：

WITH StartSegment AS (
    SELECT
        [Date],
        -- 提取'-'左侧的完整起始日期时间字符串（去除前后空格）
        LTRIM(RTRIM(LEFT([Date], CHARINDEX('-', [Date]) - 1))) AS StartFullStr
    FROM YourTable
)
SELECT
    -- 起始日期
    LTRIM(RTRIM(CONVERT(DATE, LTRIM(RTRIM(LEFT(StartFullStr, CHARINDEX(' ', StartFullStr))))))) AS StartDate,
    -- 起始时间：提取空格右侧的时间部分，转换后格式化
    LTRIM(RTRIM(REPLACE(FORMAT(
        CAST(RTRIM(RTRIM(RIGHT(StartFullStr, LEN(StartFullStr) - CHARINDEX(' ', StartFullStr)))) AS DATETIME),
        'hh:mm tt'
    ), 'AM', 'am'))) AS StartTime
FROM StartSegment

逻辑说明 #

1. 提取起始段：通过LEFT([Date], CHARINDEX('-', [Date]) - 1)取出-左侧的内容，这部分是完整的起始日期+时间（如2/23/23 9:00 am或2/23/2023 9:00 am）
2. 分隔日期与时间：利用日期和时间之间的空格作为分隔符，通过CHARINDEX(' ', StartFullStr)找到空格位置，右侧的内容就是纯时间字符串
3. 转换与格式化：将纯时间字符串转换为DATETIME类型后，用FORMAT函数输出为hh:mm tt格式，再通过REPLACE把大写的AM/PM转为小写的am/pm，匹配期望输出

原逻辑出错原因 #

原来的逻辑通过PATINDEX('%/[12][0-9] %', ...)匹配年份后的空格，当年份是4位时，这个正则无法准确匹配到日期与时间之间的空格，导致截取的字符串不是有效的时间格式，最终触发转换错误。新逻辑完全不依赖年份位数，只依赖固定的空格分隔符，可靠性更高。

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