How to generate hours between two hours in SQL Server?

Question

I want to generate a list of hours between to hours with an interval of 30 minutes.

For example an employee enters work at 09:00 and leaves at 18:00, so I want to generate this:

Hours
-----
09:00
09:30
10:00
10:30
11:00
11:30
12:00
12:30
13:00
13:30
14:00
14:30
15:00
15:30
16:00
16:30
17:00
17:30
18:00

How can I generate this? Thanks.

show all your data from the table – Matt Apr 07 '16 at 10:39 — Matt, Apr 07 '16 at 10:39

Krishnraj Rana · Accepted Answer · 2016-04-07T10:55:59.680

6

Well using recursive CTE, you can achieve this result.

Try below query -

DECLARE @timeFrom TIME = '09:00'
DECLARE @timeTo TIME = '18:00'

;with SourceHrs
as
(
    select @timeFrom as [Hours]
    UNION ALL
    SELECT DATEADD(MINUTE, 30, [Hours]) from SourceHrs WHERE [Hours] < @timeTo
)
SELECT CONVERT(VARCHAR(5),Hours,108) FROM SourceHrs

Result

Hours
-------
09:00
09:30
10:00
10:30
11:00
11:30
12:00
12:30
13:00
13:30
14:00
14:30
15:00
15:30
16:00
16:30
17:00
17:30
18:00

edited Apr 07 '16 at 10:55

answered Apr 07 '16 at 10:40

Krishnraj Rana

6,516
2
29
36

Thank you for your answer your code works correctly. I get this result: 09:00:00.0000000, how can I get this format: 09:00, thanks – Esraa_92 Apr 07 '16 at 10:46
1

Best to worry about formatting in the presentation layer. – David Rushton Apr 07 '16 at 10:50
1

@Esraa_92: I have updated my answer. You need to use `CONVERT(VARCHAR(5),Hours,108)` in the select statement. Now you get the expected output. – Krishnraj Rana Apr 07 '16 at 10:56

t-clausen.dk · Answer 2 · 2016-04-07T11:19:26.743

4

This will give you what you need, using a tally is faster than recursive:

DECLARE @from time = '09:00'
DECLARE @to time =  '09:00'

IF @from <= @to
WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a)
SELECT top (datediff(minute, @from, @to)/ 30 + 1 ) 
  LEFT(dateadd(minute, (N - 1 )*30, @from), 5)
FROM tally

edited Apr 07 '16 at 11:19

answered Apr 07 '16 at 10:58

t-clausen.dk

43,517
12
59
92

score 1 · Answer 3 · answered Mar 19 '19 at 16:27

You can also try while loop

DECLARE @strattime TIME = '09:00' DECLARE @endtime TIME = '18:00'

CREATE TABLE #tmp_hours( [BetweenHours] VARCHAR(5) )

WHILE @strattime <= @endtime BEGIN    INSERT INTO #tmp_hours values(@strattime)   SET @strattime = DATEADD(minute,30,@strattime) END

SELECT * FROM #tmp_hours DROP TABLE #tmp_hours

How to generate hours between two hours in SQL Server?

3 Answers3