Here's a function for doing dateadd for weekdays only. (e.g., what weekday is 43 weekdays from today?) Hope it is helpful.
CREATE FUNCTION dbo.weekday_dateadd ( @nbrOfDays int, @startDate smalldatetime )
RETURNS smalldatetimeASBEGIN
/* Title: weekday_dateadd Author: Jesse Roberts Date: 08/03/2005 Purpose: Adds a specified number of weekdays to a given date and returns the resulting date*/
declare @adj_start smalldatetime, @endDate smalldatetimedeclare @adj_nbrOfDays real, @adj_dayofweek intdeclare @numweeks int, @adj_enddays int, @calendar_days int
-- get to the last Monday prior to the start dateset @adj_dayofweek = (datepart(dw,@startDate) - 2)set @adj_start = dateadd(d, -(@adj_dayofweek), @startDate)
-- round to the nearest multiple of 5set @adj_nbrOfDays = round(@nbrOfDays / 5, 0) * 5
-- adjust the rounding product to ensure the result is the last multiple of 5 less than @incrementif @adj_nbrOfDays > @nbrOfDays begin set @adj_nbrOfDays = @adj_nbrOfDays - 5 end
-- add up the number of calendar days to add back to the end dateset @adj_enddays = (@nbrOfDays - @adj_nbrOfDays) + @adj_dayofweek
if @adj_enddays > 4 -- if @adj_enddays is > 4, then it means Friday of the week was passed since 2 (Mon) + 4 = 6 (Fri) begin set @adj_enddays = @adj_enddays + 2 -- add 2 days to compensate for the Sat/Sun wrap end
set @calendar_days = (@adj_nbrOfDays * 1.4) + @adj_enddays
set @endDate = dateadd(d, @calendar_days, @adj_start)
set @endDate = case datepart(dw,@endDate) when 7 then dateadd(d,2,@endDate) when 1 then dateadd(d,2,@endDate) else @endDate end
RETURN @endDateEND