CODEWITHSUNDEEP
sqlsql-servert-sql
user3140982
user3140982

Reputation: 135

Subtract Business Days from a Specific date

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?

Upvotes: 8

Views: 6971

Answers (3)

Amjad
Amjad

Reputation: 92

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

Business Days Calc Using Recursive CTE

Upvotes: 3

M.Ali
M.Ali

Reputation: 69524

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.

Upvotes: 2

Z .
Z .

Reputation: 12837

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.

Upvotes: 6

Related Questions