如何在CASE语句中使用between？SQL跨天时间段匹配班次查询异常求解

问题根因 #

• 核心错误出在@time between '17:00:00' and '06:30:00'的判断逻辑：字符串按字典序比较时，开头为1的'17:00:00'远大于开头为0的'06:30:00'，这个区间不存在任何符合条件的值，所以CASE判断永远走Else分支返回1，就是你得到的错误结果。
• 额外注意：你当前的写法是拿当前系统时间计算班次，再匹配production表中对应班次的记录，如果你的需求是给表内每一行的业务时间判断班次，那取getdate()的逻辑也不符合需求。

修复方案 #

方案1：按当前系统时间匹配对应班次的生产记录 #

直接调整CASE的判断条件，拆分跨天的时间区间：

declare @time varchar(50)
set @time = convert(varchar,getdate(),108)

select Shift_FK_ID from production
where op_Date='2021-08-23' and Shift_FK_ID=
(CASE WHEN @time >= '17:00:00' OR @time <= '06:30:00' 
Then '2'
Else '1'
End)

方案2：给production表每一行记录的业务时间计算班次 #

如果你的表中存储了每条记录对应的业务时间字段（例如op_time），需要按每条记录的时间判断班次，写法如下：

select 
    *,
    CASE WHEN op_time >= '17:00:00' OR op_time <= '06:30:00' THEN 2 ELSE 1 END as calculated_shift_id
from production
where op_Date='2021-08-23'

优化建议 #

尽量不要用varchar类型做时间判断，可直接使用SQL原生的time类型，避免字符串比较的隐性问题，示例如下：

declare @time time = getdate()
-- 后续判断逻辑和上面一致

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