sql - 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;