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.