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 http://www.kodyaz.com/articles/sql-server-dates-table-using-tsql-cte-calendar-table.aspx  to create my query.  Here is the result:

 DECLARE @TotalWorkingDays INT

 DECLARE @StartDate  DATETIME
 DECLARE @EndDate  DATETIME
 DECLARE @CurrentDate DATETIME
...

SET DATEFIRST 7;

WITH CTE_DatesTable
 AS
 (
   SELECT DATEPART (dw,@StartDate) as [dn], @StartDate AS [date]
   UNION ALL
   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)
 OPTION (MAXRECURSION 0);

 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.