How to get working days between two dates in SQL Server

Posted on Updated on

This post is about how to find the working day between Two Dates.

Consider the working days from Monday to Friday. We know that DATEDIFF function is used to calculate the Difference between two dates.

CREATE FUNCTION [dbo].fn_CountOfWorkingDays
( @StartDate DATETIME
  ,@EndDate  DATETIME
)
RETURNS  INT
AS
BEGIN
     DECLARE @CountOfWorkingDays INT
     SELECT @CountOfWorkingDays= ((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 @CountOfWorkingDays
END

Output:

SELECT [dbo].[fn_CountOfWorkingDays] ( '2017-01-01','2017-01-31') CountOfWorkingDays 
GO
CountOfWorkingDays
22

In above function  DATEDIFF(dd,@StartDate,@EndDate ) + 1 gives us  the number of days between the two dates and  DATEDIFF(wk,@StartDate,@EndDate ) this gives the number of weeks between dates and * 2 gives us the weekend (Saturday and Sunday) count.CASE statement will excludes the day if it’s a Saturday or Sunday.

In case you want to calculate working days from Monday to Saturday then just remove last case statement and * 2 from  (DATEDIFF(wk, @StartDate, @EndDate) * 2)

ALTER FUNCTION [dbo].fn_CountOfWorkingDays
( @StartDate DATETIME, 
 @EndDate    DATETIME )
RETURNS  INT
AS
BEGIN
 DECLARE @CountOfWorkingDays INT
 SELECT @CountOfWorkingDays=
             ((DATEDIFF(dd, @StartDate, @EndDate) + 1)
 -(DATEDIFF(wk, @StartDate, @EndDate) )
 -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END) )
RETURN @CountOfWorkingDays
END

Output:

SELECT [dbo].[fn_CountOfWorkingDays] ( '2017-02-01','2017-02-28') CountOfWorkingDays 
GO
CountOfWorkingDays
24

 

Advertisements

One thought on “How to get working days between two dates in SQL Server

    […] Source: How to get working days between two dates in SQL Server […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s