SQL get time values between a time range

Question

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

Do you need results as comma separator value ?? – Esty Oct 09 '16 at 10:38 — Esty, Oct 09 '16 at 10:38
I need it different rows for each values – Techy Oct 09 '16 at 10:41 — Techy, Oct 09 '16 at 10:41

TheGameiswar · Accepted Answer · 2016-10-09T11:11:19.707

4

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



;With cte(stime)
as
(
SELECT 
cast(cast( @date_value as datetime)
  + CONVERT(CHAR(8), @starttime, 108) as time)
union all
select
 cast(dateadd(minute,@interval,stime) as time)
from cte
where cast(dateadd(minute,@interval,stime) as time)<@endtime 
)
select * from cte

edited Oct 09 '16 at 11:11

answered Oct 09 '16 at 10:39

TheGameiswar

27,855
8
56
94

I need the start time also, – Techy Oct 09 '16 at 10:54
just add start time,your output format is not clear,so update question for any changes – TheGameiswar Oct 09 '16 at 10:55
From the above query I am getting results as 08:20,08:40....I need 08:00 also.This should be the first row, – Techy Oct 09 '16 at 10:56
I have included the statement ino the answer; `SELECT @starttime as stime UNION` – Techy Oct 09 '16 at 11:00
Sorry bro,that is wrong..Including the above statement is giving me repeated values – Techy Oct 09 '16 at 11:02
I have modified the query like this,`SELECT @starttime as stime union all select dateadd(minute,@interval,stime) from cte where dateadd(minute,@interval,stime)<@endtime `.Its working – Techy Oct 09 '16 at 11:09

Pரதீப் · Answer 2 · 2016-10-09T10:51:52.397

2

Here is one way without LOOP or Recursion

;with cte as
(
SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n as seq
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
),time_gen as 
(
SELECT Dateadd(MINUTE, @interval * seq , Cast(Concat(@date_value, ' ', @starttime) AS DATETIME)) as dates
FROM   cte
)
select cast(dates as time) times from time_gen
WHERE  dates < Cast(Concat(@date_value, ' ', @endtime) AS DATETIME)
order by times

Note : If you not using SQL SERVER 2012+, then use + for concatenation instead of Concat function

edited Oct 09 '16 at 10:51

answered Oct 09 '16 at 10:48

Pரதீப்

91,748
19
131
172

1

Better than recursion. I will up you. – Esty Oct 09 '16 at 10:51
Thanks for your advice – Techy Oct 09 '16 at 10:51
@Techy, I think http://stackoverflow.com/questions/32096103/selecting-n-rows-in-sql-server will be useful for you. – Esty Oct 09 '16 at 10:52

score 1 · Answer 3 · answered Oct 09 '16 at 10:37

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

DECLARE @IterateTime AS TIME(0) = @starttime
DECLARE @Diff AS INT = (SELECT DATEDIFF(MINUTE, @starttime, @endtime)) / @interval
DECLARE @Iterator AS INT = 0


WHILE @Iterator < @Diff
BEGIN
SELECT @IterateTime = DATEADD(MINUTE, @interval, @IterateTime)
SELECT @Iterator = @Iterator + 1
SELECT @IterateTime
END

SQL get time values between a time range

3 Answers3