Transact-SQL Cookbook

5.13 Excluding Recurrent Events

5.13.1 Problem

You have two dates, and you want to calculate the number of working days between them. This means that you must exclude Saturdays and Sundays from the calculation. For example, let's calculate the number of working days between January 1, 2001 and March 1, 2001.

5.13.2 Solution

Use the following query, which makes use of the Pivot table and the DATEPART function to exclude weekend days:

SELECT COUNT(*) No_working_days, DATEDIFF(day,'2002-1-1','2002-3-1') No_days FROM Pivot WHERE DATEADD(day,i,'2002-1-1') BETWEEN '2002-1-1' AND '2002-3-1' AND DATEPART(weekday, DATEADD(d,i,'2002-1-1')) BETWEEN 2 AND 6

The query calculates the number of working and calendar days between the two dates:

No_working_days No_days --------------- ----------- 44 59

5.13.3 Discussion

Querying the Pivot table generates sequential numbers that are then translated into dates using the DATEADD function. The first part of the WHERE clause uses BETWEEN to restrict the result set to only those dates between the two dates of interest. The second part of the WHERE clause makes use of the DATEPART function to determine which dates represent Saturdays and Sundays and eliminates those dates from the result. You are left with the working days.

The COUNT(*) function in the SELECT list counts up the working days between the two dates, and the DATEDIFF function in the SELECT list returns the number of calendar days between the two dates.