You could use CASE in the addition bit e.g.
declare @start date = '2020-01-01'
declare @end date = dateadd(dd, 10, @start)
declare @daystoadd int = 3
;WITH sampledatelist AS
(
SELECT @start AS datum
UNION ALL
SELECT dateadd(DAY, 1, datum)
FROM sampledatelist
WHERE dateadd(day, 1, datum) < @end
)
The CTE above just generates some dates. Here is the query that finds the next working day
select datum,
DATEADD(DD, @daystoadd, datum)
AS JustAddedOn,
DATEPART(DW, DATEADD(DD, @daystoadd, datum))
AS DayOfJustAddedOn,
CASE WHEN DATEPART(DW, DATEADD(DD, @daystoadd, datum)) = 7 THEN
DATEADD(DD, @DAYSTOADD + 2, DATUM)
WHEN DATEPART(DW, DATEADD(DD, @daystoadd, datum)) = 1 THEN
DATEADD(DD, @DAYSTOADD + 1, DATUM)
ELSE
DATEADD(DD, @daystoadd, datum)
END as calcdate
from sampledatelist
which gives results
datum JustAddedOn Day calcdate
2020-01-01 2020-01-04 7 2020-01-06
2020-01-02 2020-01-05 1 2020-01-06
2020-01-03 2020-01-06 2 2020-01-06
2020-01-04 2020-01-07 3 2020-01-07
2020-01-05 2020-01-08 4 2020-01-08
2020-01-06 2020-01-09 5 2020-01-09
2020-01-07 2020-01-10 6 2020-01-10
2020-01-08 2020-01-11 7 2020-01-13
2020-01-09 2020-01-12 1 2020-01-13
2020-01-10 2020-01-13 2 2020-01-13
Caveats
- check your localisation settings for what constitutes a weekend for your part of the world!
- This doesn't handle national holidays etc
Incidentally, I personally intensely dislike converting dates to strings just to add a time (that's just a me thing). I prefer to add the required number of hours (or in this case seconds) to the "start" of the day in question e.g.
declare @starttime INT = DATEDIFF(SECOND,0,CAST('08:00:00' AS datetime))
select dateadd(second, @starttime, dateadd(dd, datediff(dd, 0, dateadd(day, -5, current_timestamp)), 0)) as [Start Date],
dateadd(second, @starttime - 1, dateadd(dd, datediff(dd, 0, dateadd(day, -4, current_timestamp)), 0)) as [End Date]
Lastly, if I was doing a lot of comparing dates with workdays and holidays I would build up a date table containing all the useful stuff such as date, day of the week, quarter, financial period, working day etc etc then use that a reference in other queries, joining on date. You could even have a pre-calculated "next working day" column