我有以下查询,我想获取 2 个日期时间之间 30 分钟间隔的日期时间。基本上我得到了它,但它是有限的,如果时间差超过 24 小时,则不会返回所有结果。
例如:
@DateTime1 = 24/11/2016 18:00:00
@DateTime2 = 25/11/2016 06:00:00
结果:(格式为“dd-HH:mm”)
24-18:00
24-18:30
24-19:00
24-19:30
24-20:00
...
...
25-05:00
25-05:30
25-06:00
我尝试过的。
SELECT number, DATEADD(MINUTE, number, @DateTime1) AS DateTimeLine, DATEPART(DAY, DATEADD(MINUTE, number, @DateTime1)) AS Days, DATEPART(MONTH,
DATEADD(MINUTE, number, @DateTime1)) AS Months, DATEPART(YEAR, DATEADD(MINUTE, number, @DateTime1)) AS Years, DATEPART(HOUR, DATEADD(MINUTE,
number, @DateTime1)) AS Hours, DATEPART(MINUTE, DATEADD(MINUTE, number, @DateTime1)) AS Minute, CAST(DATEADD(MINUTE, number, @DateTime1)
AS DATE) AS Date, CAST(DATEADD(MINUTE, number, @DateTime1) AS TIME) AS Time
FROM master.dbo.spt_values
WHERE (type = 'P') AND (DATEPART(MINUTE, DATEADD(MINUTE, number, @DateTime1)) = 30 OR DATEPART(MINUTE, DATEADD(MINUTE, number, @DateTime1)) = 0) AND (DATEADD(MINUTE, number, @DateTime1) <= @DateTime2)
ORDER BY number