0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.