Friday, July 19, 2013

HOWTO get business days between two dates using TSQL

Ok, so I looked around and found varying formulas for calculating the number of business days between two dates and all of them had shortcomings that made them unreliable so I went about making my own.  Now I do borrow code from  to create my query.  Here is the result:

 DECLARE @TotalWorkingDays INT



WITH CTE_DatesTable
   SELECT DATEPART (dw,@StartDate) as [dn], @StartDate AS [date]
   SELECT DATEPART (dw,DATEADD(dd, 1, [date])) as [dn], DATEADD(dd, 1, [date])
   FROM CTE_DatesTable
   WHERE DATEADD(dd, 1, [date]) <= @EndDate
 SELECT @TotalWorkingDays =  COUNT([dn]) FROM CTE_DatesTable WHERE [dn] NOT IN (7,1)

 SELECT @TotalWorkingDays

What happens with this query is that it counts the days between the start and end dates excluding Saturday and Sunday (7 and 1 using DATEPART with the dw argument). 

Note: this query does NOT take in to consideration any holidays.