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.