How to split time into hourly slot using SQL (can use view,or stored proc or function)

Question

Example I have data in table which has start date, end date and duration. I want to show hourly time slot.

logic:

• Condition 1. If start date =9:00 and end date = 11:00 then show the date as

  09:00-10:00
  10:00-11:00 
  

  It should repeat 2 times and all related column data will also repeat 2 times. this will continue if time slot is suppose 11:00- 14:00 then

  11:00-12:00
  12:00-13:00
  13:00-14:00
  

  It should repeat 3 times.

• Condition 2: If start date is 9:30 and end date is 10:30 then time should round up. i.e. start date should be 9:00 and end date should be 11:00

How can I achieve this in Sql Server?

Answer 1

I assume that your issue is getting multiple rows from one, rather than formatting the date/time values as a string.

For this, you can use a recursive CTE:

with cte as (
      select startdate as thetime, t.*
      from t
      union all
      select dateadd(hour, 1, cte.startdate), . . .  -- rest of columns here
      from cte
      where cte.thetime < cte.enddate
     )
select cte.*
from cte;

You can then format thetime however you like, including the hyphenated version in your question.

SQL Server has a default limit of 100 for recursion -- the number of rows produced. Your example only uses times, so this can't exceed 24 and is not an issue. However, it could be an issue in other circumstances in which case option (maxrecursion 0) can be added to the query.