CODEWITHSUNDEEP
sqlsql-server
Daybreaker
Daybreaker

Reputation: 1047

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 SQL Server.

Bit more explained.

For example, 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 in SQL Server? I can use a loop and do this but there will be performance issues. I tried with a recursive CTE, but it didn't work.

Thanks in advance. :)

Upvotes: 0

Views: 1575

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93724

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 < CAST(@enddate AS DATE)) 
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.

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can use CTE as below:


Declare @StartDate datetime = '2017-07-26 10:30:00'
Declare @EndDate  datetime = '2017-07-29 16:45:00'



;with Cte_dates as (
    Select dateadd(dd,1,Datediff(dd,0,@StartDate)) as StDate
    Union All
    Select DATEADD(dd,1, StDate) from Cte_dates where StDate < DAteADd(dd, -2, @endDate)
)
Select @StartDate as StartSlot, DateAdd(mi, -1,dateadd(dd,1,Datediff(dd,0,@StartDate))) as EndSlot 
Union all
Select StDate as StartSlot,Dateadd(mi,-1, dateadd(dd,1,stDate)) EndSlot  from Cte_dates --Generates from CTE for in between slots
Union all
Select Dateadd(dd,0,Datediff(dd,0,@EndDate)) as StartSlot, @EndDate as EndSlot
Output as below:


+-------------------------+-------------------------+
|        StartSlot        |         EndSlot         |
+-------------------------+-------------------------+
| 2017-07-26 10:30:00.000 | 2017-07-26 23:59:00.000 |
| 2017-07-27 00:00:00.000 | 2017-07-27 23:59:00.000 |
| 2017-07-28 00:00:00.000 | 2017-07-28 23:59:00.000 |
| 2017-07-29 00:00:00.000 | 2017-07-29 16:45:00.000 |
+-------------------------+-------------------------+

Upvotes: 1

Related Questions