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

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 ]