Peter Larsson Blog

Patron Saint of Lost Yaks

How to calculate the number of weekdays in a month

by Peter Larsson in  , , ,

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.fnMonthWeekDays
(
    @Year SMALLINT,
    @Month TINYINT
)
RETURNS TINYINT
AS
BEGIN
    RETURN (
                SELECT 20 + SUM(1 - DATEDIFF(DAY, '17530101', dt) % 7 / 5)
                FROM    (
                            SELECT DATEADD(MONTH, 12 * @Year - 22800 + @Month, -1) AS dt
                            WHERE   ISDATE(10000 * @Year + 100 * @Month + 31) = 1
 
                            UNION ALL
 
                            SELECT DATEADD(MONTH, 12 * @Year - 22800 + @Month, -2)
                            WHERE   ISDATE(10000 * @Year + 100 * @Month + 30) = 1
 
                            UNION ALL
 
                            SELECT CASE ISDATE(10000 * @Year + 100 * @Month + 29)
                                        WHEN 1 THEN DATEADD(MONTH, 12 * @Year - 22800 + @Month, -3)
                                        ELSE '18991231'
                                    END
                        ) AS d
                WHERE   @Year BETWEEN 1753 AND 9999
                        AND @Month BETWEEN 1 AND 12
            )
END

Legacy Comments
Sowmya
2009-10-22		 re: How to calculate the number of weekdays in a month
Can u please explain the logic ?
Peso
2009-10-23		 re: How to calculate the number of weekdays in a month
The first 28 days of any month always has 20 weekdays, no matter what day of week the month starts with.
So, you you only have to check day 29, 30, and 31 for weekday.