How Can I Show All Time Between 2 Different Time Parameter

I have 3 Columns in db Time Parameter(00:15) , StartTime(09:00) , EndTime (15:00) Now I want to show all time with gap of 00:15 min between 09:00 and 15:00 What query should I writ

Solution 1:

Using CTE and assuming hour part of @time is zero:

declare@timetime(0) ='00:15', 
        @starttime(0) ='12:00', 
        @endtime(0) ='15:00'

;with cte as (
    select@start sTime, dateadd(minute, datepart(minute,@time), @start) eTime
    unionallselect eTime, dateadd(minute, datepart(minute,@time), eTime)
    from cte
    where dateadd(minute, datepart(minute,@time), eTime) <=@end
)
selectleft(sTime,5) +' - '+left(eTime, 5) results 
from cte

--results12:00-12:1512:15-12:3012:30-12:4512:45-13:0013:00-13:1513:15-13:3013:30-13:4513:45-14:0014:00-14:1514:15-14:3014:30-14:4514:45-15:00

Solution 2:

Use a Common table expression (CTE) to generate a table with all the times in it that you want.

Declare@strtDt smallDatetime ='15 May 2013 09:00';
Declare@endDt smallDateTime ='15 May 2013 15:00';
With DateTimes(dt) As
    (Select@strtDtUnionAllSelect DateAdd(minute, 15, dt)
     From DateTimes
     Where dt <@endDt) 
Select dt from DateTimes 
option (maxrecursion 10000)