Peter Larsson Blog

Patron Saint of Lost Yaks

How to calculate the number of weekdays for any given period

by Peter Larsson in  , , , ,

Here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-in-a-month.aspx is a specialized version for a month, and here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-number-of-weekdays-in-a-year.aspx is a specialized version for a year.

This code below calculates the number of weekdays for any given period.


CREATE FUNCTION dbo.fnPeriodWeekdays
(
    @StartDate DATETIME,
    @EndDate DATETIME
)
RETURNS INT
AS
BEGIN
    RETURN  (
                DATEDIFF(DAY, @StartDate, @EndDate) + 1
                + (DATEDIFF(DAY, '17530101', @StartDate) + 1) / 7 * 2
                - (DATEDIFF(DAY, '17530101', @EndDate) + 1) / 7 * 2
                - CASE DATEDIFF(DAY, '17530101', @StartDate) % 7
                      WHEN 6 THEN 1
                      ELSE 0
                  END
                - CASE DATEDIFF(DAY, '17530101', @EndDate) % 7
                      WHEN 5 THEN 1
                      ELSE 0
                  END
        )
END