Get every hour for a time range

Question

So what I am trying to is generate all the hours that are inside a specific time range.

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)

Thanks

Answer 1

No loops, recursive CTEs or numbers table required.

DECLARE 
  @start TIME(0) = '11:00 AM', 
  @end   TIME(0) =  '2:00 PM';

WITH x(n) AS 
(
  SELECT TOP (DATEDIFF(HOUR, @start, @end) + 1) 
  rn = ROW_NUMBER() OVER (ORDER BY [object_id]) 
  FROM sys.all_columns ORDER BY [object_id]
)
SELECT t = DATEADD(HOUR, n-1, @start) FROM x ORDER BY t;

Answer 2

You could use a recursive CTE. This would generate the hours between 11 and 14:

;with   Hours as
        (
        select  11 as hr
        union all
        select  hr + 1
        from    Hours
        where   hr < 14
        )
select  *
from    Hours

Live example at SQL Fiddle.

Answer 3

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)

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

Answer 4

The easiest way I can think of to do this is to have only 1 permanent table with a list of all hours; 24 entries total.

Create table dbo.Hours (Hourly_Time Time NOT NULL)
Insert into dbo.Hours ...

Then, given times A & B:

select * from dbo.Hours where Hourly_Time<=A and Hourly_Time>=B