Daybreaker Daybreaker - 1 year ago 78
SQL Question

Get Time Slots between two dates in sql server

I am not good in dealing with dates in sql .I want to get full and partial day time slots between two dates in MS SQL Server.
Bit more explained.
ex :
My date range is below.

StartDate - '2017-07-26 10:30:00'
End Date - '2017-07-29 16:45:00'


I want to populate time slots like below.

slot 1 - 2017-07-26 10:30:00 - 2017-07-26 23:59:00
slot 2 - 2017-07-27 00:00:00 - 2017-07-27 23:59:00
slot 3 - 2017-07-28 00:00:00 - 2017-07-28 23:59:00
slot 4 - 2017-07-29 00:00:00 - 2017-07-29 16:45:00


26 th and 29 th are not full days.
Does anyone have an idea how to achieve this sql server. I can use a loop and do this but there will be performance issues. I tried with
recursive CTE
but it didn't work.

Thanks in Advance. :)

Answer Source

This can be easily done using calendar table.. Here is one approach using Recursive CTE which generates dates on the fly

DECLARE @StartDate DATETIME = '2017-07-26 10:30:00', 
        @EndDate   DATETIME = '2017-07-29 16:45:00'; 

WITH cte 
     AS (SELECT @StartDate st 
         UNION ALL 
         SELECT Cast(Cast(Dateadd(dd, 1, st) AS DATE) AS DATETIME) 
         FROM   cte 
         WHERE  st < Dateadd(dd, -1, @enddate)) 
SELECT st, 
       CASE 
         WHEN @EndDate < Dateadd(mi, -1, Cast(Dateadd(dd, 1, Cast(st AS DATE)) AS DATETIME)) THEN @EndDate 
         ELSE Dateadd(mi, -1, Cast(Dateadd(dd, 1, Cast(st AS DATE)) AS DATETIME) ) 
       END AS ed 
FROM   cte 

I suggest you to create a physical calendar table to do this.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download