Search This Blog

Get the number of working days between two dates

CREATE FUNCTION dbo.GetWorkingDays
( @StartDate datetime,
@EndDate datetime )
RETURNS INT
AS
BEGIN
DECLARE @WorkDays int, @FirstPart int
DECLARE @FirstNum int, @TotalDays int
DECLARE @LastNum int, @LastPart int
IF (DATEDIFF(day, @StartDate, @EndDate) < 2)
BEGIN
RETURN ( 0 )
END
SELECT
@TotalDays = DATEDIFF(day, @StartDate, @EndDate) - 1,
@FirstPart = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 6
WHEN 'Monday' THEN 5
WHEN 'Tuesday' THEN 4
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 2
WHEN 'Friday' THEN 1
WHEN 'Saturday' THEN 0
END,
@FirstNum = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 5
WHEN 'Monday' THEN 4
WHEN 'Tuesday' THEN 3
WHEN 'Wednesday' THEN 2
WHEN 'Thursday' THEN 1
WHEN 'Friday' THEN 0
WHEN 'Saturday' THEN 0
END
IF (@TotalDays < @FirstPart)
BEGIN
SELECT @WorkDays = @TotalDays
END
ELSE
BEGIN
SELECT @WorkDays = (@TotalDays - @FirstPart) / 7
SELECT @LastPart = (@TotalDays - @FirstPart) % 7
SELECT @LastNum = CASE
WHEN (@LastPart <> 0) THEN @LastPart - 1
ELSE 0
END
SELECT @WorkDays = @WorkDays * 5 + @FirstNum + @LastNum
END
RETURN ( @WorkDays )
END
GO

1 comment:

Ciofu said...

OR this way:

CREATE FUNCTION dbo.fn_GetWorkingDays
(
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS INT
AS
BEGIN

DECLARE @i INT
DECLARE @WorkDays INT
DECLARE @DaysNo INT
DECLARE @DatePivot DATETIME



SET @i = 0
SET @WorkDays = 0
SET @DaysNo = DATEDIFF(DD, @StartDate, @EndDate) + 1

WHILE @i <= @DaysNo - 1
BEGIN
SET @DatePivot = DATEADD(DD, + @i, @StartDate)

IF DATENAME(DW, @DatePivot) NOT IN ('Saturday', 'Sunday') SET @WorkDays = @WorkDays + 1

SET @i = @i + 1
END

RETURN ( @WorkDays )
END