CountBusinessDays

A good functions that everyone can use to Calculate Business Days between two dates.

Click here to view the original link


Excerpts:

Working with Access, I needed to calculate business days between
dates, excluding holidays, and with an eventual conversion fo the DB to
SQL Server, rewrote the VBA into an SQL function, below:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

— =============================================

— Author: James Igoe

— Create date: 2008-08-29

— Description: Calculates business days between 2 dates

— =============================================

ALTER FUNCTION [dbo].[Fx_WorkdayCount]

(

@sStartDate As DATETIME,

@sEndDate As DATETIME

)

RETURNS SMALLINT

AS

BEGIN

— Declare the return variable here

DECLARE @BusinessDays SMALLINT

DECLARE @intDaysTotal As SMALLINT

DECLARE @intDaysWeeks As SMALLINT

DECLARE @intRemainder As SMALLINT

DECLARE @intRemainderPost As SMALLINT

DECLARE @intRemainderPre As SMALLINT

SET @intDaysTotal = DateDiff(day, @sEndDate, @sStartDate)

SET @intDaysWeeks = ((@intDaysTotal / 7) * 5)

SET @intRemainder = @intDaysTotal % 7

IF @intRemainderPre = 1

SET @intRemainder = @intRemainder - 1

ELSE IF @intRemainderPre = 7

SET @intRemainder = @intRemainder - 2

IF @intRemainderPost = 1

SET @intRemainder = @intRemainder - 1

ELSE IF @intRemainderPost = 7

SET @intRemainder = @intRemainder - 2

IF (@intRemainderPost < @intRemainderPre) And @intRemainderPre <> 7

SET @intRemainder = @intRemainder - 2