By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with dates as (
select convert(date, '2019-10-04') as originaldate
union all
select dateadd(day, 1, originaldate)
from dates
where originaldate < '2019-10-12'
)
select * into t from dates
9 rows affected
with cte as (
select min(originaldate) as dte, count(*) as cnt, min(originaldate) as originaldate
from t
union all
select (case when datename(weekday, v.nextdate) = 'Saturday' then dateadd(day, 2, nextdate)
when datename(weekday, v.nextdate) = 'Sunday' then dateadd(day, 1, nextdate)
else v.nextdate
end),
cnt - 1, dateadd(day, 1, originaldate)
from cte cross apply
(values (dateadd(day, 1, dte))) v(nextdate)
where cnt > 0
)
select originaldate, dte
from cte
order by originaldate
| originaldate | dte |
|---|---|
| 2019-10-04 | 2019-10-04 |
| 2019-10-05 | 2019-10-07 |
| 2019-10-06 | 2019-10-08 |
| 2019-10-07 | 2019-10-09 |
| 2019-10-08 | 2019-10-10 |
| 2019-10-09 | 2019-10-11 |
| 2019-10-10 | 2019-10-14 |
| 2019-10-11 | 2019-10-15 |
| 2019-10-12 | 2019-10-16 |
| 2019-10-13 | 2019-10-17 |