How to Count Work Days Between 2 Dates in SQL Server

Introduction

This is a real quick post to explain how you can calculate the number of work days between 2 dates in SQL Server. This post considers the Workdays from Monday to Friday. In case you want the workdays from Monday to Saturday, read on. So, let us write a function that will display the number of work days between 2 dates in SQL Server.

CREATE FUNCTION [dbo].[CalculateNumberOFWorkDays] (@StartDate datetime, @EndDate datetime)
RETURNS int
AS
BEGIN
     
     SET @StartDate = DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0) 
     SET @EndDate = DATEADD(dd, DATEDIFF(dd, 0, @EndDate), 0) 
          
     DECLARE @WORKDAYS INT
     SELECT @WORKDAYS = (DATEDIFF(dd, @StartDate, @EndDate) + 1)
	               -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
   		       -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
		       -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
  
     RETURN @WORKDAYS
END

In case you want the workdays to be calculated from Monday to Saturday, use the function below instead:

CREATE FUNCTION [dbo].[CalculateNumberOFWorkDays] (@StartDate datetime, @EndDate datetime)
RETURNS int
AS
BEGIN
     
     SET @StartDate = DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0) 
     SET @EndDate = DATEADD(dd, DATEDIFF(dd, 0, @EndDate), 0) 
          
     DECLARE @WORKDAYS INT
     SELECT @WORKDAYS = (DATEDIFF(dd, @StartDate, @EndDate) + 1)
	               -(DATEDIFF(wk, @StartDate, @EndDate))
   		       -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  
     RETURN @WORKDAYS
END

Hope you like this post. In case you know any other method of calculating the work days, let us know via comments. Cheers!