# Thinking outside the box

Patron Saint of Lost Yaks

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