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

Weft

so there's a good reason you need to do this in Sql instead of c#? – Luxate

I have edited your title. Please see, "Should questions include “tags” in their titles?", where the consensus is "no, they should not". – Lafleur

@JohnSaunders I learned once again something new. Thanks ;) – Weft

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.

Race

Hello. I got the following msg : The statement terminated. The maximum recursion 100 has been exhausted before statement completion. – Weft

@Weft - My bad check now – Sybil

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

Buddy

Answer 3

The existing answers are outdated for current versions of SQL Server, since there are much more elegant ways to do this today. In case the following question gets closed to point here, I wanted to make sure this post had a modern answer as well.

• How populate row in SQL Server with date and time

On SQL Server 2022 (or Azure SQL DB/MI), you can use GENERATE_SERIES:

DECLARE @start datetime = '20140113 02:00:00',
        @end   datetime = '20141231 14:00:00';

SELECT h = DATEADD(HOUR, value, @start)
  FROM GENERATE_SERIES(0, DATEDIFF(HOUR, @start, @end)) AS x;

On SQL Server 2016 or better, you can use STRING_SPLIT(REPLICATE:

DECLARE @start datetime = '20140113 02:00:00',
        @end   datetime = '20141231 14:00:00';

SELECT h = DATEADD(HOUR, ROW_NUMBER() OVER (ORDER BY @@SPID)-1, @start)
  FROM STRING_SPLIT
  (REPLICATE(CONVERT(varchar(max), ','), 
   DATEDIFF(HOUR, @start, @end)), ',') AS x;

On older versions, the other answers here will work.

Here's a db<>fiddle but with a shorter date range because it doesn't gracefully handle thousands of output rows.

Hairdresser