Thinking outside the box

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

My Links

Advertisement

News

Archives

Post Categories

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.

Powered by:
Powered By Subtext Powered By ASP.NET