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

To add to @GordonLinoff 's good answer, Jonathan Roberts (from SQLServerCentral.com - See the link in the revision history in the code for the original article) wrote a dandy function that'll handle pretty much anything. The flower box pretty much explains it all along with some example usage Here's his code from his article.

/**********************************************************************************************************************
 FUNCTION: DateRange
 Returns a table of datetime values based on the parameters
 Parameters:  
 @StartDate    :Start date of the series 
 @EndDate      :End date of the series 
 @DatePart :The time unit for @interval
     ns    : nanoseconds 
     mcs   : microseconds 
     ms    : milliseconds 
     ss    : seconds
     mi    : minutes
     hh    : hours
     dd    : days
     ww    : weeks
     mm    : months
     qq    : quarters
     yy    : years
 @Interval :The number of dateparts between each value returned

 Sample Calls:
     SELECT * FROM [dbo].[DateRange]('2011-01-01 12:24:35', '2011-02-01 12:24:35', 'ss', 2);
     SELECT COUNT(*) FROM [dbo].[DateRange]('2018-01-01 00:00:00', '2018-01-25 20:31:23.646', 'ms', default);
     SELECT * FROM [dbo].[DateRange]('2011-01-01', '2012-02-03', default, default);
     SELECT * FROM [dbo].[DateRange]('2012-02-03', '2011-01-01', 'dd', 7);
     SELECT DATEDIFF(ns,'2018-01-01 00:00:00.000', value),Value,* 
       FROM [dbo].[DateRange]('2018-01-01 00:00:00.000', '2018-01-01 00:00:00.00001', 'ns', 100);

-----------------------------------------------------------------------------------------------------------------------

 Revision History:
 Rev 00 - 29 Aug 2019 - Jonathan Roberts
                      - Initial release
                      - Ref: https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function
**********************************************************************************************************************/
CREATE FUNCTION [dbo].[DateRange] 
(
    @StartDate datetime2, 
    @EndDate   datetime2, 
    @DatePart  nvarchar(3)='dd', 
    @Interval  int=1
)
RETURNS TABLE AS RETURN 
  WITH A(A) AS (SELECT 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) A(A)),
       B(RowNum) AS (SELECT TOP(ABS(CASE @DatePart
                                        WHEN 'ns'  THEN DATEDIFF(ns, @EndDate, @StartDate)/@Interval
                                        WHEN 'mcs' THEN DATEDIFF(mcs,@EndDate, @StartDate)/@Interval
                                        WHEN 'ms'  THEN DATEDIFF(ms, @EndDate, @StartDate)/@Interval
                                        WHEN 'ss'  THEN DATEDIFF(ss, @EndDate, @StartDate)/@Interval
                                        WHEN 'mi'  THEN DATEDIFF(mi, @EndDate, @StartDate)/@Interval
                                        WHEN 'hh'  THEN DATEDIFF(hh, @EndDate, @StartDate)/@Interval
                                        WHEN 'dd'  THEN DATEDIFF(dd, @EndDate, @StartDate)/@Interval
                                        WHEN 'ww'  THEN DATEDIFF(ww, @EndDate, @StartDate)/@Interval
                                        WHEN 'mm'  THEN DATEDIFF(mm, @EndDate, @StartDate)/@Interval
                                        WHEN 'qq'  THEN DATEDIFF(qq, @EndDate, @StartDate)/@Interval
                                        WHEN 'yy'  THEN DATEDIFF(yy, @EndDate, @StartDate)/@Interval
                                        ELSE DATEDIFF(dd, IIF(@StartDate < @EndDate, @StartDate, @EndDate), IIF(@StartDate < @EndDate, @EndDate, @StartDate))/@Interval
                                    END) + 1)
                            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
                       FROM A A, A B, A C, A D, A E, A F, A G, A H)   -- A maximum of 16^8 (or 2^32) rows could be returned from this inline tally
  SELECT CASE @DatePart            
              WHEN 'ns'  THEN DATEADD(ns, T.AddAmount, @StartDate)
              WHEN 'mcs' THEN DATEADD(mcs,T.AddAmount, @StartDate)
              WHEN 'ms'  THEN DATEADD(ms, T.AddAmount, @StartDate)
              WHEN 'ss'  THEN DATEADD(ss, T.AddAmount, @StartDate)
              WHEN 'mi'  THEN DATEADD(mi, T.AddAmount, @StartDate)
              WHEN 'hh'  THEN DATEADD(hh, T.AddAmount, @StartDate)
              WHEN 'dd'  THEN DATEADD(dd, T.AddAmount, @StartDate)
              WHEN 'ww'  THEN DATEADD(ww, T.AddAmount, @StartDate)
              WHEN 'mm'  THEN DATEADD(mm, T.AddAmount, @StartDate)
              WHEN 'qq'  THEN DATEADD(qq, T.AddAmount, @StartDate)
              WHEN 'yy'  THEN DATEADD(yy, T.AddAmount, @StartDate)
              ELSE            DATEADD(dd, T.AddAmount, @StartDate)
          END [Value]
    FROM B
   CROSS APPLY(VALUES (IIF(@StartDate<@EndDate, @interval*RowNum, @interval*-RowNum))) T(AddAmount)
GO

Answer 2

A numbers/tally table would do the trick as Gordon mentioned. However, I use a UDF to create dynamic date ranges.

For example

Select * from [dbo].[udf-Create-Range-Date]('2016-07-10 08:00','2016-07-10 09:00','MI',10)

Returns

RetVal
2016-07-10 08:00:00.000
2016-07-10 08:10:00.000
2016-07-10 08:20:00.000
2016-07-10 08:30:00.000
2016-07-10 08:40:00.000
2016-07-10 08:50:00.000
2016-07-10 09:00:00.000

The UDF

CREATE FUNCTION [dbo].[udf-Create-Range-Date] (@DateFrom datetime,@DateTo datetime,@DatePart varchar(10),@Incr int)

Returns 
@ReturnVal Table (RetVal datetime)

As
Begin
    With DateTable As (
        Select DateFrom = @DateFrom
        Union All
        Select Case @DatePart
               When 'YY' then DateAdd(YY, @Incr, df.dateFrom)
               When 'QQ' then DateAdd(QQ, @Incr, df.dateFrom)
               When 'MM' then DateAdd(MM, @Incr, df.dateFrom)
               When 'WK' then DateAdd(WK, @Incr, df.dateFrom)
               When 'DD' then DateAdd(DD, @Incr, df.dateFrom)
               When 'HH' then DateAdd(HH, @Incr, df.dateFrom)
               When 'MI' then DateAdd(MI, @Incr, df.dateFrom)
               When 'SS' then DateAdd(SS, @Incr, df.dateFrom)
               End
        From DateTable DF
        Where DF.DateFrom < @DateTo
    )

    Insert into @ReturnVal(RetVal) Select DateFrom From DateTable option (maxrecursion 32767)

    Return
End

-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','YY',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','DD',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-31','MI',15) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-02','SS',1) 

Answer 3

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

Answer 4

A numbers table can solve your problem. Assuming you don't need more than a few thousand rows, then this should work:

with n as (
      select row_number() over (order by (select null)) - 1 as n
      from master.spt_values
     )
select d.*,
       dateadd(minute, n.n * @intervalMinutes, d.startTime)
from @myDates d join
     n
     on dateadd(minute, n.n * @intervalMinutes, d.startTime) <= d.endTime;