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 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 ]

Feedback

Gravatar

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

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

# 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.

Powered by:
Powered By Subtext Powered By ASP.NET