时间戳

首页 > 解决方案 > 在 SQL Server 存储过程中查找当前时间之前最接近的值

sql-server - 在 SQL Server 存储过程中查找当前时间之前最接近的值

问题描述

我的表中有 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 @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 @Currentdate = GETDATE()  
SET @Currenttime = (SELECT CAST(@Currentdate AS TIME))
SET @PreviousShifttime = (SELECT DATEADD(HOUR, -8, @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
)
select *
from shifts
where convert(datetime, @PreviousShifttime) between shift_start and shift_end
   or dateadd(day, 1, convert(datetime, @PreviousShifttime)) between shift_start and shift_end

此过程正确返回当前班次行。但我希望之前的班次行没有硬编码 -8 小时基于上一次班次结束时班次开始的最接近值

标签： sql-serverstored-procedures

解决方案

尝试这个：

declare @shift table(
    shiftno     int,
    shiftstart  time(7),
    shiftend    time(7)
)

insert into @shift values
(1, '07:00', '14:59:59'),
(2, '15:00', '22:59:59'),
(3, '23:00', '06:59:59') -- I changed shiftend here

SELECT p.*
FROM @shift c
JOIN @shift p ON c.shiftstart=DATEADD(SECOND,1,p.shiftend)
WHERE CAST(GETDATE() AS time) BETWEEN c.shiftstart AND c.shiftend

第二种变体：

declare @shift table(
    shiftno     int,
    shiftstart  time(7),
    shiftend    time(7)
)

insert into @shift values -- I changed all the shiftend here
(1, '07:00', '15:00'),
(2, '15:00', '23:00'),
(3, '23:00', '07:00')

DECLARE @CurTime time=CAST(GETDATE() AS time)

SELECT p.*
FROM @shift c
JOIN @shift p ON c.shiftstart=p.shiftend
WHERE @CurTime>=c.shiftstart
  AND @CurTime<c.shiftend

推荐阅读