Find the working days based on the start and end day

CREATE
FUNCTION Dbo.WorkingDaysCount
(@StartDate DATETIME,@EndDate DATETIME)
RETURNS INT
AS

BEGIN

DECLARE @CurrentDate DATETIME

DECLARE @Days INT

DECLARE @IncrementValue INT

SET @Days=0

SET @IncrementValue=0

WHILE @IncrementValue<=(DATEDIFF(DAY,@StartDate,@EndDate))

BEGIN

-- Get a Current Date between two date

SET @CurrentDate=DATEADD(DAY,@IncrementValue,@StartDate)

 

IF (DATENAME(WEEKDAY,@CurrentDate)
!='Sunday'
            AND
DATENAME(WEEKDAY,@CurrentDate)
!='Saturday' )

BEGIN

SET @Days=@Days+1 -- Increment Working Days Counts

END

SET @IncrementValue=@IncrementValue+1

END

RETURN @Days

END
SELECT dbo.WorkingDaysCount('6/1/2011','6/30/2011') AS 'Working Days'