T-SQL Function to Calculate Working Days Between Given Dates

This T-SQL function for SqlServer 2008 calculates the number of working days between two given dates.

IF OBJECT_ID('dbo.fnGetWorkingDays') IS NOT NULL
DROP FUNCTION dbo.fnGetWorkingDays;
GO

CREATE FUNCTION dbo.fnGetWorkingDays
( @pStartDate DATE,
@pEndDate DATE,
@pWorkingDays VARCHAR(7) = 'YYYYYNN' )
RETURNS INT
AS
BEGIN

DECLARE
@cMonday INT = DATEPART(dw,CONVERT(DATE,'29 OCT 2001',103)),
@vCheckDate DATE = @pStartDate,
@vDay INT,
@vDuration INT,
@vNoOfWeeks INT

IF @pStartDate IS NOT NULL AND @pEndDate IS NOT NULL
BEGIN

SET @vDuration = 0

WHILE @vCheckDate <= @pEndDate
BEGIN
SET @vDay = CAST(REPLACE((DATEPART(dw,@vCheckDate)+8-@cMonday)%7,'0','7') AS INT);
IF DATEDIFF(day,@vCheckDate,@pEndDate) < 7 OR @vDay != 1
BEGIN
IF SUBSTRING(@pWorkingDays,@vDay,1) = 'Y'
SET @vDuration = @vDuration + 1;
SET @vCheckDate = DATEADD(day,1,@vCheckDate);
END
ELSE
BEGIN
SET @vNoOfWeeks = FLOOR(DATEDIFF(day,@vCheckDate,@pEndDate)/7);
SET @vCheckDate = DATEADD(day,@vNoOfWeeks*7,@vCheckDate);
SET @vDuration = @vDuration+(@vNoOfWeeks*LEN(REPLACE(@pWorkingDays,'N','')));
END
END

END;

RETURN(@vDuration);

END
GO

To query the function for working days between 10th October 2009 and 11th November 2009

SELECT dbo.fnGetWorkingDays('10/10/2009','11/11/2009',DEFAULT) 'Working Days'
=
Working Days
23

This figure is for the default working days, Monday to Friday. This can be changed by providing a pWorkingDays parameter. The parameter contains seven characters of either Y or N. The first character is for Monday, second for Tuesday and so on. A Y represents it is a working day, an N represents it is a non-working day.

For those lucky enough not to work on a Wednesday, the same query would look like.

SELECT dbo.fnGetWorkingDays('10/10/2009','11/11/2009','YYNYYNN')
=
Working Days
18

Done