title: SQL Server function to add working days to a date
body: <p>I have seen a number of postings regarding how to create an SQL Server function that will add a given number of working days to a date.  But, none of them calculate exactly the way I need.  We have a function that is currently done in code but that I want to move to a native SQL Server function (for use by stored procedures and in queries).  We give our customers 5 working days to collect cargo before we start assessing storage charges.  The 5 working days exclude weekends and holidays (we have a table with the holiday dates).  The trick here is that I need to get the date immediately after the 5 working days regardless of whether it is a weekend or holiday.  So I need the function to return the last working date, NOT the first working day after. So, for example:</p>
  
  <pre><code>Oct 20th (Sat) plus 5 working days = Oct 26th (Fri)
  Oct 21st (Sun) plus 5 working days = Oct 26th (Fri)
  Oct 22nd (Mon) plus 5 working days = Oct 29th (Mon)
  May 19th (Sat) plus 5 working days with May 21st a holiday = May 28th
  </code></pre>
  
  <p>The 5 working days is the current allotment, but this may change in the future so the number of working days needs to be a parameter.  Also, the function may be used over fairly large datasets so I would prefer doing this without loops.  We are running SQL Server 2008.</p>
  
  <p>Edit: This is not a duplicate of "<a href="https://stackoverflow.com/questions/5471524">Add business days to date in SQL without loops</a>" as they want the ending date to be a working day.  I want my ending date to be whatever date immediately follows the last grace day (ie: 5 working days Mon to Fri I want the Sat date returned, NOT the following Mon).</p>