T-SQL - DateTime Gap Distribution

Question

Code:

DECLARE @SD DATE    = '2017-01-01'
        ,@ED DATE   = '2017-01-07'
        ,@ST TIME   = '08:00:00'
        ,@ET TIME   = '16:00:00';

DECLARE @DT_T TABLE (SDT DATETIME, EDT DATETIME)

Goal: To combine Start/End Date and Start/End Time (@SD/@ED and @ST/@ET) and create a table with gap intervals between StartDateTime and EndDateTime as shown in the desired output below.

Desired Output

/* @DT_T Data
SDT                         EDT
2017-01-01 08:00:00         2017-01-01 16:00:00
2017-01-02 08:00:00         2017-01-02 16:00:00
2017-01-03 08:00:00         2017-01-03 16:00:00
2017-01-04 08:00:00         2017-01-04 16:00:00
2017-01-05 08:00:00         2017-01-05 16:00:00
2017-01-06 08:00:00         2017-01-06 16:00:00
2017-01-07 08:00:00         2017-01-07 16:00:00
*/

I'm trying with the numbers table but so far not getting anywhere close.

Answer 1

Something like this? rextester: http://rextester.com/ULTV27021

declare @sd date    = '2017-01-01'
        ,@ed date   = '2017-01-07'
        ,@st_hour int = 8
        ,@et_hour int = 16;

declare @dt_t table (sdt datetime, edt datetime);

;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, d as (
  select 
      sdt=dateadd(hour,@st_hour,convert(datetime,dateadd(day, row_number() over (order by (select 1)) -1,@sd)))
    , edt=dateadd(hour,@et_hour,convert(datetime,dateadd(day, row_number() over (order by (select 1)) -1,@sd)))
    from         n as deka
      cross join n as hecto
      cross join n as kilo     /* 2.73 years */
      --cross join n as [10k]    /* 27.3 years */
)
insert into @dt_t (sdt,edt)
  select top (datediff(day,@sd,@ed)+1)
      sdt
    , edt
    from d
    order by sdt;

select * from @dt_t 

note: rextester's default output formatting for dates is dd.MM.yyyy