Add business days to date in SQL without loops

Question

I currently have a function in my SQL database that adds a certain amount of business days to a date, e.g. if you enter a date that is a Thursday and add two days, it will return the date of the following Monday. I'm not bothered about any holidays, only weekends are excluded.

The problem is that this is currently done using a while loop, and it appears to be massively slowing down the stored procedure that uses it while generating a table. Does anyone know if there is any way to perform this calculation without while loops or cursors?

Just for information, this is the current function:

ALTER FUNCTION [dbo].[AddWorkDaysToDate] (    @fromDate       datetime, @daysToAdd      int ) RETURNS datetime AS BEGIN    DECLARE @toDate datetime DECLARE @daysAdded integer  -- add the days, ignoring weekends (i.e. add working days) set @daysAdded = 1 set @toDate = @fromDate  while @daysAdded <= @daysToAdd begin     -- add a day to the to date     set @toDate = DateAdd(day, 1, @toDate)     -- only move on a day if we've hit a week day     if (DatePart(dw, @toDate) != 1) and (DatePart(dw, @toDate) != 7)     begin         set @daysAdded = @daysAdded + 1     end end  RETURN @toDate  END 

Answer 1

This is better if anyone is looking for a TSQL solution. No loops, no tables, no case statements AND works with negatives. Can anyone beat that?

CREATE FUNCTION[dbo].[AddBusinessDays](@Date date,@n INT) RETURNS DATE AS  BEGIN DECLARE @d INT;SET @d=4-SIGN(@n)*(4-DATEPART(DW,@Date)); RETURN DATEADD(D,@n+((ABS(@n)+@d-2)/5)*2*SIGN(@n)-@d/7,@Date); END