Techy Techy - 1 year ago 94
SQL Question

SQL get time values between a time range

I have to calculate all the time values which are between a particular start time and end time on the basis of a particular time interval on a date in stored procedure in SQL.

I have 4 parameters for my stored procedure.

@date_value nvarchar(1000),//date value'2016-10-09'
@starttime TIME(0) //= '08:00',//suppose starttime is 8am
@endtime TIME(0) //= '13:00'
@interval INT //= '20' //20 minutes

I am trying to get all the time values between the starttime and endtime. I need it like this.
8:00,8:20,08:40........upto 12:50(end value I dont need).

I have googled it and found that we can use

SELECT DATEADD(MINUTE, @MinutesToAdd, @StartTime)

Here I am not able to include the end time.Please help me

Answer Source
@date_value nvarchar(1000)='2016-10-09',
@starttime TIME(0)= '08:00',
@endtime TIME(0) = '13:00',
@interval INT = '20' 

;With cte(stime)
cast(cast( @date_value as datetime)
  + CONVERT(CHAR(8), @starttime, 108) as time)
union all
 cast(dateadd(minute,@interval,stime) as time)
from cte
where cast(dateadd(minute,@interval,stime) as time)<@endtime 
select * from cte
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download