How to select all hours between two dates?

Question

declare @minDateTime as datetime;
declare @maxDateTime as datetime;

set @minDateTime = '2014-01-13 02:00:00';
set @maxDateTime = '2014-12-31 14:00:00';

I am looking to create a select statement that would return every hour between @minDateTime and @maxDateTime as follows (there is no table to select from. I am not looking for where clause !):

2014-01-13 02:00:00
2014-01-13 03:00:00
2014-01-13 04:00:00
...
2014-12-31 12:00:00
2014-12-31 13:00:00
2014-12-31 14:00:00

Answer 1

Try this. Use a Recursive CTE.

DECLARE @minDateTime AS DATETIME;
DECLARE @maxDateTime AS DATETIME;

SET @minDateTime = '2014-01-13 02:00:00';
SET @maxDateTime = '2014-12-31 14:00:00';

;
WITH Dates_CTE
     AS (SELECT @minDateTime AS Dates
         UNION ALL
         SELECT Dateadd(hh, 1, Dates)
         FROM   Dates_CTE
         WHERE  Dates < @maxDateTime)
SELECT *
FROM   Dates_CTE
OPTION (MAXRECURSION 0) 

In the above query Dates_CTE is a Common Expression Table, the base record for the CTE is derived by the first sql query before UNION ALL. The result of the query gives you the Minimum date.

Second query after UNION ALL is executed repeatedly to get results. This process is recursive and will continue till the Dates is less than @maxDateTime.

Answer 2

Here is another way using a Tally Table:

DECLARE @minDateTime DATETIME;
DECLARE @maxDateTime DATETIME;

SET @minDateTime = '2014-01-13 02:00:00';
SET @maxDateTime = '2014-12-31 14:00:00';

DECLARE @hrsDiff INT;
SELECT @hrsDiff = DATEDIFF(HH, @minDateTime, @maxDateTime);

WITH E1(N) AS (
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
),--10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
Tally(N) AS(SELECT row_number() over(order by (select null)) from E4) -- Numbered rrow
SELECT @minDateTime 
UNION ALL
SELECT DATEADD(HH, N, @minDateTime)
FROM Tally
WHERE
    N <= @hrsDiff