因此,我试图生成的是特定时间范围内的所有小时。
因此,鉴于范围从上午11点到下午2:00,我将得到:
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