sqlserver生成时间序列

Question

sqlserver如何生成一个时段内的逐小时时间序列？

根据一个开始时间、结束时间，最后生成一个整小时的时间序列呢，效果如下

Answer 1

DECLARE @start DATETIME = '2021-05-05 02:00:00'
DECLARE @end DATETIME = '2021-05-05 17:00:00'

;WITH CTE AS (
    SELECT @start AS HourStart
    UNION ALL
    SELECT DATEADD(HOUR, 1, HourStart)
    FROM CTE
    WHERE DATEADD(HOUR, 1, HourStart) <= @end
)
SELECT HourStart
FROM CTE
OPTION (MAXRECURSION 0)

OPTION (MAXRECURSION 0)来避免递归次数限制的问题