Getting 30 minute slots between time

Question

I have a Start Datetime and a End Datetime.

Eg: 10:00 am - 12:00 pm

I have to create 4 slots between the, each ranging for 30 minutes.

Eg:  
10:00 am-10:30 am
10:30 am-11:00 am
11:00 am-11:30 am
11:30 am-12:00 pm

This 30 minutes can vary and is not a constant. I have tried a few things but they don't seem to work. Can someone please help. Thank you.

I have tried this but i only get the slot difference not the slotfrom-slotto

with cte as  
(  
select   convert(datetime,CONVERT(VARCHAR(15), getdate(), 112)) as DATE   
union all    
select   c.DATE+'00:20:00' as QuarterTime  
from cte c   
where c.date<dateadd(minute,1422,convert(datetime,CONVERT(VARCHAR(15), getdate(), 112)))
)
select * from cte

Answer 1

My idea is not far from yours.

DECLARE @dtStart AS DATETIME ='20151028 10:00:00'
        ,@dtEnd AS DATETIME = '20151028 12:00:00'
        ,@iInterval AS INT = 30;  --30 min interval


WITH aCTE
AS(
    SELECT 
        @dtStart AS StartDateTime,
        DATEADD(MINUTE,@iInterval,@dtStart) AS EndDateTime
    UNION ALL
    SELECT 
        DATEADD(MINUTE,@iInterval,StartDateTime),
        DATEADD(MINUTE,@iInterval,EndDateTime)
    FROM aCTE
    WHERE
        DATEADD(MINUTE,@iInterval,EndDateTime) <= @dtEnd
)

SELECT 
    -- 10:00:00 AM 
    CONVERT(VARCHAR(10),StartDateTime,108) 
    + ' ' + RIGHT(CONVERT(VARCHAR(30), StartDateTime, 9), 2) 
    + ' - ' +
    -- 10:30:00 AM
    CONVERT(VARCHAR(10),EndDateTime,108) 
    + ' ' + RIGHT(CONVERT(VARCHAR(30), EndDateTime, 9), 2) AS Result
FROM aCTE

And the output is:

Result
10:00:00 AM - 10:30:00 AM
10:30:00 AM - 11:00:00 AM
11:00:00 AM - 11:30:00 AM
11:30:00 AM - 12:00:00 PM

Answer 2

This works:

• without recursive CTE
• with any value in minute for @slot
• for up to 100 slots but it can be easily extended to 1.000, 10.000 ...

• the CTE only create a range of number from 0 to 99

  Declare @start time = '10:00am';
  Declare @end time = '12:00pm';
  Declare @slot int = 30;
  
  With inc(n) as (
      Select TOP(DATEDIFF(minute, @start, @end)/@slot) ROW_NUMBER() over(order by (select 1))-1 
      From (
          Select 1 From (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x1(n) -- 0-9
          Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x2(n) -- 0-99
          --Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x3(n) -- 0-999
      ) as x(n)
  )
  Select [start] = DATEADD(minute, @slot*n, @start) 
      , [end] = DATEADD(minute, @slot*(n+1), @start)
      , [slots] = CONVERT(varchar(10), DATEADD(minute, @slot*n, @start), 100)
      + ' - ' + CONVERT(varchar(10), DATEADD(minute, @slot*(n+1), @start), 100)
   From inc;
  

Output:

    start               end                 slots
    10:00:00.0000000    10:30:00.0000000    10:00AM - 10:30AM
    10:30:00.0000000    11:00:00.0000000    10:30AM - 11:00AM
    11:00:00.0000000    11:30:00.0000000    11:00AM - 11:30AM
    11:30:00.0000000    12:00:00.0000000    11:30AM - 12:00PM