How To Write Recursive Cte In Sql Server 2012 For Hour And Min

I would like to generate a list of half an hour interval. Any suggestion would be very helpful. I tried this and did not work. Thank you WITH cte AS (select convert(varchar, DATEAD

Solution 1:

You can't convert the time to a VARCHAR and then use DATETIME functions on it, you need to keep it as a DATETIME throughout the recursive portion:

;WITH cte AS (SELECT DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())) dt
              UNION  ALL
              SELECT DATEADD(MINUTE, 30, dt) AS dt
              FROM cte
              WHERE dt < DATEADD(day,1,GETDATE())
              )
SELECT CONVERT(VARCHAR(12),dt, 108)
FROM  cte