CODEWITHSUNDEEP
sqlrecursive-query
poshan
poshan

Reputation: 3287

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, DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate())), 108)  AS Today

    UNION ALL

    SELECT dateadd(MINUTE, 30, Today) AS Today
    FROM cte
    WHERE dateadd(MINUTE, 30,Today) < (select convert(varchar, DATEADD(Day, 1, DATEDIFF(Day, 0, GetDate())), 108))
    )
SELECT*
FROM cte

To get:

0:00 
0:30
1:00
1:30
2:00
2:30
3:00
3:30
4:00
4:30
5:00
5:30
6:00
6:30
7:00
7:30
8:00
8:30
9:00
9:30
10:00
10:30
11:00
11:30
12:00
12:30
13:00
13:30
14:00
14:30
15:00
15:30
16:00
16:30
17:00
17:30
18:00
18:30
19:00
19:30
20:00
20:30
21:00
21:30
22:00
22:30
23:00
23:30
0:00

Upvotes: 1

Views: 1819

Answers (1)

Hart CO
Hart CO

Reputation: 34774

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

Upvotes: 1

Related Questions