 05/10/2013 by Nitesh

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

Friends,

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, lets 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!