sqlserver生成时间序列

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

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

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)来避免递归次数限制的问题

DECLARE @StartTime DATETIME = '2021-05-05 02:00:00';
DECLARE @EndTime DATETIME = '2021-05-05 17:00:00';

WITH HourlySequence AS (
    SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, @StartTime), 0) AS Hour
    UNION ALL
    SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, @StartTime) + 1, 0) AS Hour
    FROM HourlySequence
    WHERE DATEADD(HOUR, DATEDIFF(HOUR, 0, @StartTime) + 1, 0) <= @EndTime
)
SELECT Hour
FROM HourlySequence;