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 for any given period

Here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-in-a-month.aspx is a specialized version for a month, and here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-number-of-weekdays-in-a-year.aspx is a specialized version for a year.

This code below calculates the number of weekdays for any given period.


CREATE FUNCTION dbo.fnPeriodWeekdays
(
    @StartDate DATETIME,
    @EndDate DATETIME
)
RETURNS INT
AS
BEGIN
    RETURN  (
                DATEDIFF(DAY, @StartDate, @EndDate) + 1
                + (DATEDIFF(DAY, '17530101', @StartDate) + 1) / 7 * 2
                - (DATEDIFF(DAY, '17530101', @EndDate) + 1) / 7 * 2
                - CASE DATEDIFF(DAY, '17530101', @StartDate) % 7
                      WHEN 6 THEN 1
                      ELSE 0
                  END
                - CASE DATEDIFF(DAY, '17530101', @EndDate) % 7
                      WHEN 5 THEN 1
                      ELSE 0
                  END
        )
END

Print | posted on Sunday, July 12, 2009 2:29 PM | Filed Under [ Optimization 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