如何在 SQL Server 2012 中为小时和分钟编写递归 CTE [英] 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

获得:

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

推荐答案

你不能把时间转换成 VARCHAR 然后在上面使用 DATETIME 函数，你需要在整个递归部分将其保留为 DATETIME:

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

这篇关于如何在 SQL Server 2012 中为小时和分钟编写递归 CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！