Geek Answers Handbook

Subtract business days from a specific date

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

+8
sql sql-server tsql
source share
3 answers

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

Calculating Business Days Using Recursive CTE

+3
source share

One way to do this is to pre-create a table with all the dates for a couple of years in advance and select from this table. Thus, you can mark Saturday, Sunday, holidays, etc.

+6
source share
 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 Saturday and Sunday if you want to exclude National Holidays and other seasonal holidays that you will need to use the calendar table, as Zdrako Danev already mentioned.

+2
source share

More articles:


All Articles