Script to generate a time dimension type result set with year month and hour in separate columns

Question

I would like a generate a table with all the rows for every hour 0-23 for the next 10 years storing the year, month, day and hour all in separate columns like so:

Year | Month | Day | Hour

2021 | 01 | 01 | 01
2021 | 01 | 01 | 02
2021 | 01 | 01 | 03
2021 | 01 | 01 | 04
2021 | 01 | 01 | 05
2021 | 01 | 01 | 06
.....

Anyone have an easy way to do this?

Thanks!

Answer 1

Use two recursions. One is for the dates and the other is for the hours. And then use CROSS APPLY:

DECLARE @startDate date = '2021-01-01';
DECLARE @endDate date = '2031-01-01';
;WITH CTE_Date AS (
    SELECT @startDate AS [Date] 
    UNION ALL
    SELECT DATEADD(DAY, 1, [Date]) AS [Date]
    FROM CTE_Date 
    WHERE [Date] < @endDate
),
CTE_Hour AS (
    SELECT 0 AS [Hour]
    UNION ALL
    SELECT [Hour] + 1 AS [Hour]
    FROM CTE_Hour
    WHERE [Hour] < 23
)

SELECT YEAR(d.[Date]) AS [Year], 
       RIGHT('0' + CAST(MONTH(d.[Date]) AS varchar(2)), 2) AS [Month], 
       RIGHT('0' + CAST(DAY(d.[Date]) AS varchar(2)), 2) AS [Day], 
       RIGHT('0' + CAST(h.[Hour] AS varchar(2)), 2) AS [Hour]
FROM CTE_Date AS d
CROSS APPLY (
    SELECT [Hour] FROM CTE_Hour
) AS h
ORDER BY [Year], [Month], [Day], h.[Hour]
OPTION(MAXRECURSION 0);
GO

Answer 2

Check a solution that is based on recursion:

;
with E as
(
    select cast('2021-01-01 01:00:00' as datetime) as d
    union all
    select t.d
    from E
    cross apply (values (dateadd(hour, 1, E.d))) t(d)
    where t.d < '2031-01-01'
)
select year(d) as [year], month(d) as [month], day(d) as [day], datepart(hour, d) as [hour]
from E
order by d    
option (maxrecursion 0)

You can insert these rows to a table. If required, you can also convert the numbers to text and prepend ‘0’.

Answer 3

Thank you! Both solutions work great! The first solution is less code and completes in 3 seconds, the second solution is more lines of code and compeltes in 1 second. Both are completely fine answers. I am torn which solution to mark as the answer! I really appreciate both resonses!