获取一个时间范围内的每小时
所以我想要生成特定时间范围内的所有时间.
So what I am trying to is generate all the hours that are inside a specific time range.
所以考虑到上午 11 点到下午 2:00 的范围,我会得到:
So given the range 11 AM to 2:00 PM, I would get:
11:00 AM
12:00 PM
1:00 PM
2:00 PM
我试图避免存储商店可能开放的每个特定时间,只存储范围(我需要将时间与其他时间进行比较)
I am trying to avoid having to store every specific hour a store might be open and just store the range (I need to compare the hours against other times)
谢谢
推荐答案
如果你有一个 数字表(如果没有,请点击链接创建)...
If you have a numbers table (click the link to create one if you don't)...
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 个值的小时表.
If this is specialized, you can create an hours table with just 24 values.
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
相关文章