please help/guide with a query split that would break a 1 hour intervals

Question

I have in a SQL an agenda table in which i have the start time and end time. What i'ld like to do is to split into an hour every hour between the date time between start time and end time.

Example :

----------
 start time - end time
----------
 2017-07-17 9:00 - 2017-07-17 11:00

Would return

----------
 start time  -  end time
----------
 2017-07-17 9:00 - 2017-07-17 10:00

 2017-07-17 10:00 - 2017-07-17 11:00

Possible duplicate of [How to get total number of hours between two dates in sql server?](https://stackoverflow.com/questions/2475178/how-to-get-total-number-of-hours-between-two-dates-in-sql-server) — Ravikumar, Jul 17 '18 at 07:01

score 3 · Answer 1 · answered Jul 17 '18 at 07:17

DECLARE @date1 DATETIME = GETDATE()-1 , @date2 DATETIME = GETDATE()
DECLARE @tbl TABLE (Dates varchar(100))

WHILE @date1 <= @date2
BEGIN
    INSERT INTO @tbl( Dates )
    SELECT CONVERT(VARCHAR(50),@date1 ,106)+' '+ 
      CONCAT(DATEPART(HOUR,@date1),':','00') +' - '+ 
      CONVERT(VARCHAR(50),@date1 ,106)+' '+ 
      CONCAT(DATEPART(HOUR,@date1)+1,':','00')

    SET @date1 = DATEADD(HOUR,1,@date1)
END

SELECT Dates FROM @tbl

score 1 · Accepted Answer · answered Jul 17 '18 at 07:23

One way to do this is use CTE in MS SQL SERVER (assuming you are using MS SQL SERVER)

DECLARE @startTime DATETIME, @endTime DATETIME;
SET @startTime = CONVERT(DATETIME,'2018-07-17 01:00:00',120);
SET @endTime = CONVERT(DATETIME,'2018-07-17 17:00:00',120);
;WITH StartEnd(startTime,endTime) as  (
SELECT @startTime, DATEADD(hour,1,@startTime) -- ANCHOR QUERY
UNION ALL
SELECT endTime,DATEADD(hour,1,endTime) from StartEnd -- RECURSIVE QUERY
WHERE endTime < @endTime    
)
SELECT * from StartEnd;

score 0 · Answer 3 · answered Jul 17 '18 at 07:00

0

I think you are looking for spliting your time into hours here is the relevant question you can find your solution here.

you can found multiple answers and approaches of your problem here.

here is the link of Fiddle try it

answered Jul 17 '18 at 07:00

Imran Ahmad Shahid

793
8
29

i dont want in union – shal Jul 17 '18 at 07:02

please help/guide with a query split that would break a 1 hour intervals

3 Answers3