# Getting 30 minute slots between time

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
)
select * from cte
``````

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,
UNION ALL
SELECT
FROM aCTE
WHERE
)

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
``````

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
``````