SQL Server跨午夜班次ID字段生成方案技术求助

在SQL Server环境下，我有一张包含StartTime列的数据表，需要基于该列的日期时间值生成新的id字段。

现有StartTime示例数据 #

新id字段生成规则 #

• 日班X（001）：对应X日8:00至20:00，ID格式为yyMMdd+001
• 夜班X（002）：对应X日20:00至X+1日8:00，ID格式为yyMMdd+002

预期生成结果示例 #

尝试过的代码（存在跨天问题） #

SELECT 
    StartTime, 
    (CASE
         WHEN StartTime >= CAST(CONCAT(CONVERT(DATE, [StartTime]), ' ', '08:00:00') AS DATETIME) 
          AND StartTime <= CAST(CONCAT(CONVERT(DATE, [StartTime]), ' ', '20:00:00') AS DATETIME)  
             THEN CONCAT(FORMAT(StartTime, 'yy'),
                         FORMAT(StartTime, 'MM'), 
                         FORMAT(StartTime, 'dd'), '001')
             ELSE CONCAT(FORMAT(StartTime, 'yy'),
                         FORMAT(StartTime, 'MM'),
                         FORMAT(StartTime, 'dd'), '002') 
         END) AS ShiftId
FROM 
    table

解决方案 #

核心思路：通过将StartTime提前8小时，统一计算班次的归属日期，再结合时间区间判断班次编号，解决跨天夜班的日期归属问题。

实现代码：

SELECT 
    StartTime,
    CONCAT(
        -- 提前8小时后取日期，自动处理0-8点归属前一天的逻辑
        FORMAT(DATEADD(HOUR, -8, StartTime), 'yyMMdd'),
        -- 仅通过时间部分判断班次
        CASE WHEN CAST(StartTime AS TIME) >= '08:00:00' AND CAST(StartTime AS TIME) < '20:00:00' 
             THEN '001' 
             ELSE '002' 
        END
    ) AS ID
FROM 
    YourTableName; -- 替换为实际表名

逻辑说明 #

1. DATEADD(HOUR, -8, StartTime)：将时间往前推8小时，凌晨0-8点的时间会转化为前一天的16-24点，取日期时自动归属到前一天，匹配夜班的日期规则；8点及以后的时间仍归属当天。
2. CAST(StartTime AS TIME)：提取纯时间部分，仅判断小时区间即可区分日班/夜班，无需拼接日期，逻辑更简洁。
3. FORMAT函数确保日期格式统一为yyMMdd，符合ID的格式要求。

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