在 SQL Server 存储过程中根据时间查找正确的班次

Question

我的表格中有 8 小时的轮班，Shift和shiftstart，数据类型shiftend time(7).

ShiftNo ShiftName  ShiftStart  ShiftEnd  IsNextDay  IsBothNextDay
--------------------------------------------------------------------
   1    Shift1     7:00:00     14:59:59   0          0
   2    SHift2     15:00:00    22:59:59   0          0
   3    Shift3     23:00:00    7:00:00    1          0

如果我在 07：10 执行该过程，我应该得到 shift3 行

23:00:00.0000000-07:00:00.0000000 as timestamp 

我现有的程序是

DECLARE @Currentdate AS DATETIME
DECLARE @Currenttime AS TIME
DECLARE @PreviousShifttime AS TIME
SET @Currentdate = GETDATE()
PRINT @currentdate  
SET @Currenttime = (SELECT CAST(@Currentdate AS TIME))
PRINT @Currenttime  
SET @PreviousShifttime = (SELECT DATEADD(HOUR, -8, @Currentdate))
PRINT @PreviousShifttime  
SELECT 
    ShiftNo, ShiftName, ShiftStart, ShiftEnd, IsNextDay, IsBothNextDay 
FROM
    ShiftInfo
WHERE
    @PreviousShifttime BETWEEN ShiftStart AND ShiftEnd

当我想获取班次 3 的行时，此过程不会返回预期的输出。这种情况的输出为空

谁能告诉我哪里出错了？

Answer 1

转换为日期时间进行比较。 对于班次 3，结束班次时间变为第二天

declare @shift table
(
    shiftno     int,
    shiftstart  time(7),
    shiftend    time(7)
)
--  sample data
insert into @shift values
(1, '07:00', '14:59:59'),
(2, '15:00', '22:59:59'),
(3, '23:00', '07:00:00')
declare @Currenttime    as time
set @Currenttime = '06:30'
--  the query
; with shifts as
(
    select  *, 
        shift_start = convert(datetime, shiftstart),
        shift_end   = case  when shiftstart < shiftend
                    then convert(datetime, shiftend)
                    else dateadd(day, 1, convert(datetime, shiftend))
                    end
    from    @shift
)
select  *
from    shifts
where   convert(datetime, @Currenttime) between shift_start and shift_end
or       dateadd(day, 1, convert(datetime, @Currenttime)) between shift_start and shift_end

编辑：要获取基于@Currenttime的前一个班次，请使用以下查询

--  the query
; with 
shifts as
(
    select  *, 
        shift_start = convert(datetime, shiftstart),
        shift_end   = case  when shiftstart < shiftend
                    then convert(datetime, shiftend)
                    else dateadd(day, 1, convert(datetime, shiftend))
                    end
    from    @shift
),
current_shift as
(
    select  PrevShiftTime = dateadd(minute, -1, shiftstart)
    from    shifts
    where   convert(datetime, @Currenttime) between shift_start and shift_end
    or       dateadd(day, 1, convert(datetime, @Currenttime)) between shift_start and shift_end
)
select  *
from    shifts s
    cross join current_shift c
    where   convert(datetime, PrevShiftTime) between shift_start and shift_end
    or       dateadd(day, 1, convert(datetime, PrevShiftTime)) between shift_start and shift_end