# Thinking outside the box

Patron Saint of Lost Yaks

## How to calculate the number of weekdays in a month

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

Print | posted on Sunday, July 12, 2009 1:50 PM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

## #re: How to calculate the number of weekdays in a month

Can u please explain the logic ?
10/22/2009 3:12 PM | Sowmya

## #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.
10/23/2009 9:41 AM | Peso
Comments have been closed on this topic.