65.9K
CodeProject is changing. Read more.
Home

How to Count Work Days Between 2 Dates in SQL Server

starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.60/5 (6 votes)

Jan 9, 2014

CPOL
viewsIcon

82706

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!

How to Count Work Days Between 2 Dates in SQL Server - CodeProject