Function Calculating Working Days Between Two Dates in SQL Server

Hello to everyone,

In this article, I will try to give information about the function that calculates the working days between two dates in SQL Server.

If you are calculating the working days of the staff in SQL Server, you may need such a function. I wrote it simply. You can make it even more detailed.

You can easily do this by using the function below.

CREATE FUNCTION fn_workDaysCalculator
(
    @startDate DATETIME,
    @endDate DATETIME
)
RETURNS FLOAT
AS
BEGIN

    DECLARE @start DATETIME,
            @end DATETIME,
            @numberofDays FLOAT;

    SET @start = @startDate;
    SET @end = @endDate;



    SET @numberofDays =
    (
        SELECT (DATEDIFF(dd, @start, @end) + 1) - (DATEDIFF(wk, @start, @end) * 2)
               - (CASE
                      WHEN DATENAME(dw, @start) = 'Sunday' THEN
                          1
                      ELSE
                          0
                  END
                 ) -                         (CASE
                              WHEN DATENAME(dw, @end) = 'Saturday' THEN
                                  1
                              ELSE
                                  0
                          END
                         )
    );

    RETURN @numberofDays;
END;


--Using the Function

SELECT dbo.fn_workDaysCalculator('2021.10.04','2021.10.09') as WorkDays 
SELECT dbo.fn_workDaysCalculator('2021.10.04','2021.10.18') as WorkDays 

Build and run the function and you will see the following result.

As you can see, we calculated the working days.

Good luck to everyone in business and life.