sql server - How to generate minute intervals between two dates in T-SQL?

Question

I have a table of startTime and endTimes. I need to generate a table of intervals between those two dates in minutes. Here's some sample data:

declare @intervalMinutes int = 10
declare @myDates table (
myId int primary key identity,
startTime datetime,
endTime datetime
)

insert @myDates (startTime, EndTime) values ('2016-07-10 08:00','2016-07-10 09:00')
insert @myDates (startTime, EndTime) values ('2016-07-12 10:00','2016-07-12 12:00')
insert @myDates (startTime, EndTime) values ('2016-07-14 12:30','2016-07-14 14:30')

What I'd like to see is for each myId a set of dates of interval @intervalMinutes.

So if we had @intervalMinutes set to 10 then I'd see for the first row a list of 6 dates between 2016-07-10 08:00 and 2016-07-10 09:00 in 10 minute increments.

Answer 1

You can use recursive query like this :

declare @intervalMinutes int = 10
declare @myDates table (
myId int primary key identity,
startTime datetime,
endTime datetime
)

DECLARE @startTime DATETIME = '2016-07-10 08:00'
DECLARE @endTime DATETIME = '2016-07-10 09:00'

;WITH CTE AS
(
    SELECT  @startTime st
    UNION   ALL
    SELECT  dateadd(MINUTE,@intervalMinutes,st) st
    FROM    cte
    where   dateadd(MINUTE,@intervalMinutes,st) < @endTime
)
INSERT INTO @myDates(startTime,endTime)
SELECT st,dateadd(MINUTE,@intervalMinutes,st) FROM cte

SELECT  * FROm @myDates