CODEWITHSUNDEEP
sqlsql-servert-sql
Marc
Marc

Reputation: 9527

How to select all hours between two dates?

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

Upvotes: 10

Views: 11697

Answers (3)

user25043454
user25043454

Reputation:

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.

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.

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

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

Upvotes: 3

Pரதீப்
Pரதீப்

Reputation: 93694

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.

Upvotes: 17

Related Questions