所以我想要的是生成特定时间范围内的所有时间.
所以考虑到上午11点到下午2点,我会得到：
11:00 AM 12:00 PM 1:00 PM 2:00 PM
我试图避免必须存储商店可能打开的每个特定小时,只是存储范围(我需要比较其他时间的小时)
谢谢
解决方法
如果您有
numbers table(如果不这样做,请点击链接创建一个)…
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
