[TSQL] 產生 以每30分鐘為單位的 時間清單

DECLARE @dtStart AS DATETIME ='20200417 10:00:00'
        ,@dtEnd AS DATETIME = '20200417 23:59:00'
        ,@iInterval AS INT = 30;  --30 min interval


WITH aCTE
AS(
    SELECT 
        @dtStart AS StartDateTime,
        DATEADD(MINUTE,@iInterval,@dtStart) AS EndDateTime
    UNION ALL
    SELECT 
        DATEADD(MINUTE,@iInterval,StartDateTime),
        DATEADD(MINUTE,@iInterval,EndDateTime)
    FROM aCTE
    WHERE
        DATEADD(MINUTE,@iInterval,EndDateTime) <= @dtEnd
)

SELECT 
    -- 10:00:00 AM 
    CONVERT(VARCHAR(10),StartDateTime,114) ,
  RIGHT(CONVERT(VARCHAR(30), StartDateTime, 9), 2)  ,
    -- 10:30:00 AM
    CONVERT(VARCHAR(10),EndDateTime,114) ,
   RIGHT(CONVERT(VARCHAR(30), EndDateTime, 9), 2) AS Result
FROM aCTE