SQL Server DateTime列按时间区间判断并替换时间值的实现方法

需求说明 #

从SQL Server的DateTime类型Workstart列提取时间部分做区间判断，将时间落在07:15:00至08:30:00之间的记录，仅把时间部分替换为08:00:00，保留原日期。

测试数据 #

原查询的问题 #

你写的SQL存在几个问题：

1. CAST函数语法错误，正确写法是CAST(Workstart AS TIME)，而非cast(time,workstart)
2. CASE语句仅返回了时间字符串，没有和原日期拼接，无法得到完整的DateTime结果
3. 未处理不符合条件的记录（若需要保留原数据的话）

正确的查询语句 #

如果只是查询时返回修改后的结果，使用以下语句：

SELECT 
    CASE 
        WHEN CAST(Workstart AS TIME) BETWEEN '07:15:00' AND '08:30:00' 
        THEN DATEADD(DAY, DATEDIFF(DAY, 0, Workstart), '08:00:00')
        ELSE Workstart  -- 不需要保留原记录可删除此行，不符合条件的会返回NULL
    END AS Workstart,
    Personelnum
FROM YourTableName;  -- 替换成你的实际表名

语句解释

• CAST(Workstart AS TIME)：提取Workstart的时间部分，用于区间判断
• DATEDIFF(DAY, 0, Workstart)：计算Workstart距离1900-01-01的天数，以此保留原日期部分
• DATEADD(DAY, ..., '08:00:00')：将原日期与指定时间拼接，得到修改后的DateTime值

若需更新表中数据 #

如果要直接修改表内的记录，使用UPDATE语句：

UPDATE YourTableName
SET Workstart = DATEADD(DAY, DATEDIFF(DAY, 0, Workstart), '08:00:00')
WHERE CAST(Workstart AS TIME) BETWEEN '07:15:00' AND '08:30:00';

执行结果 #

运行上述查询后，会得到你期望的结果：

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