12,078,489 members (46,170 online)
Technical Blog
alternative version

41.2K views
9 bookmarked
Posted

# How to Count Work Days Between 2 Dates in SQL Server

, 9 Jan 2014 CPOL
 Rate this:
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!

## About the Author

 Team Leader Director @ Rebin Infotech 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.

## Comments and Discussions

 First Prev Next
 How to use this Function Member 117417784-Jun-15 4:32 Member 11741778 4-Jun-15 4:32
 For official holiday sankarsan parida9-Jan-14 23:59 sankarsan parida 9-Jan-14 23:59
 Re: For official holiday Nitesh Luharuka10-Jan-14 1:49 Nitesh Luharuka 10-Jan-14 1:49
 Last Visit: 31-Dec-99 19:00     Last Update: 13-Feb-16 15:01 Refresh 1

General    News    Suggestion    Question    Bug    Answer    Joke    Praise    Rant    Admin

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