sql – 获取一个时间范围的每小时

所以考虑到上午11点到下午2点,我会得到：

11:00 AM
 12:00 PM
 1:00 PM
 2:00 PM

我试图避免必须存储商店可能打开的每个特定小时,只是存储范围(我需要比较其他时间的小时)

谢谢

解决方法

create table test(
    startTime time,endTime time
)

insert into test
select '11:00','14:00'

select
    dateadd(hh,n.n,t.startTime) as times
from test t
  inner join Numbers n
    -- assuming your numbers start at 1 rather than 0
    on n.n-1 <= datediff(hh,t.startTime,t.endTime)

如果这是专门的,您可以创建一个只有24个值的小时表.

create table HoursInADay(
    [hours] time not null,constraint PK_HoursInADay primary key ([hours])
)

-- insert
insert into HoursInADay select '1:00'
insert into HoursInADay select '2:00'
insert into HoursInADay select '3:00'
insert into HoursInADay select '4:00'
insert into HoursInADay select '5:00'
insert into HoursInADay select '6:00'
insert into HoursInADay select '7:00'
...

select
    h.[hours]
from test t
  inner join HoursInADay h
    on h.[hours] between t.startTime and t.endTime

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!