Click here to Skip to main content
Click here to Skip to main content

Tagged as

How to Count Work Days Between 2 Dates in SQL Server

, 9 Jan 2014
Rate this:
Please Sign up or sign in to vote.
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!

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Nitesh Luharuka
Team Leader Independent IT Professional, Consultant
India India
An Independent IT Professional and Consultant on MS Technologies like .Net | MVC | Silverlight | Sharepoint | MS Project Server along with open source CMS Systems like DotNetNuke etc.
 
I am an enthusiastic technocrat who love to debug problems and solve them. I love writing articles on my website in my spare time. Please visit my Website for more details and subscribe to get technology related tips/tricks.
Follow on   Twitter   LinkedIn

Comments and Discussions

 
QuestionFor official holiday Pinprofessionalsankarsan parida9-Jan-14 22:59 
AnswerRe: For official holiday PinmemberNitesh Luharuka10-Jan-14 0:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 9 Jan 2014
Article Copyright 2014 by Nitesh Luharuka
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid