Expand the date range between two dates

I am trying to expand the date between two dates

800000 is 08:00am

16000000 is 16:00 hours(pm)

30 is 30 minutes slots

Expected output

in "Hours in day" column is 9 because of time between 08:00 am to 05:00pm is 9 hours

Tried query in but was not successful.

WITH Daterange 
AS (
    SELECT (CONVERT(datetime,'20240101' ,114)) AS MyDate
    UNION ALL
    SELECT DATEADD(DAY, 1, MyDate)
    FROM DateGenerate
    WHERE MyDate < (CONVERT(datetime,'20240105' ,114))
)
SELECT  MyDate, 45, 804000, 1203000
FROM Daterange
UNION ALL
SELECT  DEFSLOT, 0, fromtime, Totime
FROM Slottable 

This is my try

Perhaps an example will help you formulate the question more precisely.

Test data slightly changed.
Used range from 2024-01-01 09:00:00 to 2024-01-05 16:00:00.
If default time for day is startTime=08:00:00.00 endTime=17:00:00.00, then in all "generated" days startTime=08:00:00.00 endTime=17:00:00.00.

In testdata first day startTime 09:00:00 taked (from 2024-01-01 09:00:00), to chek

     StartTime as StartTime

In anchor part of query, if fromDate=ToDate (range is intraday), we take

     case when FromDate=ToDate then EndTime else 17000000 end EndTime

And for last day endTime=16:00:00 (from value 2024-01-05 16:00:00), to test last day endTime.

with DateRange as(
  select CONVERT(datetime,cast(FromDate as varchar) ,114) MyDate 
     ,StartTime
     ,case when FromDate=ToDate then EndTime else 17000000 end EndTime
     , Slot 
        --Range boundaries for recursion
     , CONVERT(datetime,cast(ToDate as varchar) ,114) ToDate
     ,StartTime as StartTime0,EndTime as EndTime0
  from SlotTable
  union all
  select dateadd(day,1,MyDate) MyDate 
     ,8000000 FromTime
     ,case when dateadd(day,1,MyDate)<ToDate then 17000000 else EndTime0 end EndTime
     , Slot 
        --Range boundaries for recursion
     ,ToDate ,StartTime0,EndTime0
  from DateRange
  where dateadd(day,1,MyDate)<=ToDate
)
select MyDate, StartTime,EndTime, Slot,(EndTime-StartTime)/1000000 Hours
from daterange

fiddle

If you look at what this task looks like, you can assume that you need to calculate the number of working hours in the specified range. This task also specifies the "business" time(8:00-17:00).
You can also add a weekend exception)