title: Add business days to date in SQL without loops
body: <p>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.</p>
  
  <p>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?</p>
  
  <p>Just for information, this is the current function:</p>
  
  <pre><code>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 &lt;= @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
  </code></pre>