# Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

## How to calculate number of weekdays in a year

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

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

Comments have been closed on this topic.