Peter Larsson Blog

Patron Saint of Lost Yaks

How to calculate number of weekdays in a year

by Peter Larsson in  , , ,

This function calculates the number of weeksdays in a year, and has error-checking for invalid years.
It is also language independant.

Here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx you can find a specialized version that calculates number of weekdays for any given period.

CREATE FUNCTION dbo.fnYearWeekDays
(

    @Year SMALLINT
)
RETURNS SMALLINT
AS
BEGIN
        RETURN  (
                    SELECT  260 + SUM(1 - DATEDIFF(DAY, '17530101', dt) % 7 / 5)
                    FROM    (
                                SELECT  DATEADD(YEAR, @Year - 1899, -1) AS dt
 
                                UNION ALL
                                                                   
                                SELECT  DATEADD(YEAR, @Year - 1899, -2)
                                WHERE   ISDATE(10000 * @Year + 229) = 1
                            ) AS d
                    WHERE   @Year BETWEEN 1753 AND 9999
                )
END