Subtract Business Days from a Specific date

Question

I need to subtract a number of business days (1-15 days) from a specific date, for example subtracting 5 business days from 2013-12-27 should return 2013-12-20, is there an easy way to do that?

Answer 1

One way to do that is to pre-create a table with all the dates for couple of years in advance and select from that table. This way you can mark saturdays, sundays, holidays, etc.

Answer 2

This post explains how to do it using a recursive CTE:

Business Days Calc Using Recursive CTE

Answer 3

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2013/10/01'
SET @EndDate = '2013/10/31'


SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATEPART(dw, @StartDate) = 1 THEN 1 ELSE 0 END)
  -(CASE WHEN DATEPART(dw, @EndDate)   = 7 THEN 1 ELSE 0 END)
                                AS [TotalWorkingDays]

Result

TotalWorkingDays
23

Important Note

This method will only ignore Saturdays and Sundays, If you want to exclude National Holidays and other seasonal Holidays you will need to make use of a calender table as Zdravko Danev has already mentioned.