How to select all hours between two dates?

sql, sql-server, t-sql

Solution

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`.

Problem

``` 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 ```

Original source