Count work days between two dates

How can I calculate the number of work days between two dates in SQL Server?

Monday to Friday and it must be T-SQL.

For workdays, Monday to Friday, you can do it with a single SELECT, like this:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008/10/01'
SET @EndDate = '2008/10/31'


SELECT
   (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)

If you want to include holidays, you have to work it out a bit...

In Calculating Work Days you can find a good article about this subject, but as you can see it is not that advanced.

--Changing current database to the Master database allows function to be shared by everyone.
USE MASTER
GO
--If the function already exists, drop it.
IF EXISTS
(
	SELECT *
	FROM dbo.SYSOBJECTS
	WHERE ID = OBJECT_ID(N'[dbo].[fn_WorkDays]')
	AND XType IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[fn_WorkDays]
GO
 CREATE FUNCTION dbo.fn_WorkDays
--Presets
--Define the input parameters (OK if reversed by mistake).
(
	@StartDate DATETIME,
	@EndDate   DATETIME = NULL --@EndDate replaced by @StartDate when DEFAULTed
)

--Define the output data type.
RETURNS INT

AS
--Calculate the RETURN of the function.
BEGIN
    --Declare local variables
    --Temporarily holds @EndDate during date reversal.
	DECLARE @Swap DATETIME

    --If the Start Date is null, return a NULL and exit.
	IF @StartDate IS NULL
		RETURN NULL

    --If the End Date is null, populate with Start Date value so will have two dates (required by DATEDIFF below).
     IF @EndDate IS NULL
        SELECT @EndDate = @StartDate

    --Strip the time element from both dates (just to be safe) by converting to whole days and back to a date.
    --Usually faster than CONVERT.
    --0 is a date (01/01/1900 00:00:00.000)
     SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate), 0),
            @EndDate   = DATEADD(dd,DATEDIFF(dd,0,@EndDate)  , 0)

	--If the inputs are in the wrong order, reverse them.
     IF @StartDate > @EndDate
        SELECT @Swap      = @EndDate,
               @EndDate   = @StartDate,
               @StartDate = @Swap

	--Calculate and return the number of workdays using the input parameters.
	--This is the meat of the function.
	--This is really just one formula with a couple of parts that are listed on separate lines for documentation purposes.
     RETURN (
		SELECT
		--Start with total number of days including weekends
		(DATEDIFF(dd,@StartDate, @EndDate)+1)
		--Subtact 2 days for each full weekend
		-(DATEDIFF(wk,@StartDate, @EndDate)*2)
		--If StartDate is a Sunday, Subtract 1
		-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday'
			THEN 1
			ELSE 0
		END)
		--If EndDate is a Saturday, Subtract 1
		-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday'
			THEN 1
			ELSE 0
		END)
		)
    END
GO

If you need to use a custom calendar, you might need to add some checks and some parameters. Hopefully it will provide a good starting point.

All Credit to Bogdan Maxim & Peter Mortensen. This is their post, I just added holidays to the function (This assumes you have a table "tblHolidays" with a datetime field "HolDate".

--Changing current database to the Master database allows function to be shared by everyone.
USE MASTER
GO
--If the function already exists, drop it.
IF EXISTS
(
	SELECT *
	FROM dbo.SYSOBJECTS
	WHERE ID = OBJECT_ID(N'[dbo].[fn_WorkDays]')
	AND XType IN (N'FN', N'IF', N'TF')
)

DROP FUNCTION [dbo].[fn_WorkDays]
GO
 CREATE FUNCTION dbo.fn_WorkDays
--Presets
--Define the input parameters (OK if reversed by mistake).
(
	@StartDate DATETIME,
	@EndDate   DATETIME = NULL --@EndDate replaced by @StartDate when DEFAULTed
)

--Define the output data type.
RETURNS INT

AS
--Calculate the RETURN of the function.
BEGIN
	--Declare local variables
	--Temporarily holds @EndDate during date reversal.
	DECLARE @Swap DATETIME

	--If the Start Date is null, return a NULL and exit.
	IF @StartDate IS NULL
		RETURN NULL

	--If the End Date is null, populate with Start Date value so will have two dates (required by DATEDIFF below).
	IF @EndDate IS NULL
		SELECT @EndDate = @StartDate

	--Strip the time element from both dates (just to be safe) by converting to whole days and back to a date.
	--Usually faster than CONVERT.
	--0 is a date (01/01/1900 00:00:00.000)
	SELECT @StartDate = DATEADD(dd,DATEDIFF(dd,0,@StartDate), 0),
			@EndDate   = DATEADD(dd,DATEDIFF(dd,0,@EndDate)  , 0)

	--If the inputs are in the wrong order, reverse them.
	IF @StartDate > @EndDate
		SELECT @Swap      = @EndDate,
			   @EndDate   = @StartDate,
			   @StartDate = @Swap

	--Calculate and return the number of workdays using the input parameters.
	--This is the meat of the function.
	--This is really just one formula with a couple of parts that are listed on separate lines for documentation purposes.
	RETURN (
		SELECT
		--Start with total number of days including weekends
		(DATEDIFF(dd,@StartDate, @EndDate)+1)
		--Subtact 2 days for each full weekend
		-(DATEDIFF(wk,@StartDate, @EndDate)*2)
		--If StartDate is a Sunday, Subtract 1
		-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday'
			THEN 1
			ELSE 0
		END)
		--If EndDate is a Saturday, Subtract 1
		-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday'
			THEN 1
			ELSE 0
		END)
		--Subtract all holidays
		-(Select Count(*) from [DB04\DB04].[Gateway].[dbo].[tblHolidays]
		  where  [HolDate] between @StartDate and @EndDate )
		)
	END  
GO
-- Test Script
/*
declare @EndDate datetime= dateadd(m,2,getdate())
print @EndDate
select  [Master].[dbo].[fn_WorkDays] (getdate(), @EndDate)
*/

My version of the accepted answer as a function using DATEPART, so I don't have to do a string comparison on the line with

DATENAME(dw, @StartDate) = 'Sunday'

Anyway, here's my business datediff function

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION BDATEDIFF
(
    @startdate as DATETIME,
    @enddate as DATETIME
)
RETURNS INT
AS
BEGIN
    DECLARE @res int

SET @res = (DATEDIFF(dd, @startdate, @enddate) + 1)
    -(DATEDIFF(wk, @startdate, @enddate) * 2)
    -(CASE WHEN DATEPART(dw, @startdate) = 1 THEN 1 ELSE 0 END)
    -(CASE WHEN DATEPART(dw, @enddate) = 7 THEN 1 ELSE 0 END)

    RETURN @res
END
GO

Another approach to calculating working days is to use a WHILE loop which basically iterates through a date range and increment it by 1 whenever days are found to be within Monday – Friday. The complete script for calculating working days using the WHILE loop is shown below:

CREATE FUNCTION [dbo].[fn_GetTotalWorkingDaysUsingLoop]
(@DateFrom DATE,
@DateTo   DATE
)
RETURNS INT
AS
     BEGIN
         DECLARE @TotWorkingDays INT= 0;
         WHILE @DateFrom <= @DateTo
             BEGIN
                 IF DATENAME(WEEKDAY, @DateFrom) IN('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday')
                     BEGIN
                         SET @TotWorkingDays = @TotWorkingDays + 1;
                 END;
                 SET @DateFrom = DATEADD(DAY, 1, @DateFrom);
             END;
         RETURN @TotWorkingDays;
     END;
GO

Although the WHILE loop option is cleaner and uses less lines of code, it has the potential of being a performance bottleneck in your environment particularly when your date range spans across several years.

You can see more methods on how to calculate work days and hours in this article: https://www.sqlshack.com/how-to-calculate-work-days-and-hours-in-sql-server/

 DECLARE @TotalDays INT,@WorkDays INT
 DECLARE @ReducedDayswithEndDate INT
 DECLARE @WeekPart INT
 DECLARE @DatePart INT

 SET @TotalDays= DATEDIFF(day, @StartDate, @EndDate) +1
 SELECT @ReducedDayswithEndDate = CASE DATENAME(weekday, @EndDate)
  WHEN 'Saturday' THEN 1
  WHEN 'Sunday' THEN 2
  ELSE 0 END 
 SET @TotalDays=@TotalDays-@ReducedDayswithEndDate
 SET @WeekPart=@TotalDays/7;
 SET @DatePart=@TotalDays%7;
 SET @WorkDays=(@WeekPart*5)+@DatePart

 RETURN @WorkDays

(I'm a few points shy of commenting privileges)

If you decide to forgo the +1 day in CMS's elegant solution, note that if your start date and end date are in the same weekend, you get a negative answer. Ie., 2008/10/26 to 2008/10/26 returns -1.

my rather simplistic solution:

select @Result = (..CMS's answer..)
if  (@Result < 0)
		select @Result = 0
	RETURN @Result

.. which also sets all erroneous posts with start date after end date to zero. Something you may or may not be looking for.

For difference between dates including holidays I went this way:

1. Table with Holidays:

    CREATE TABLE [dbo].[Holiday](
   

   [Id] [int] IDENTITY(1,1) NOT NULL, [Name] nvarchar NULL, [Date] [datetime] NOT NULL)

2. I had my plannings Table like this and wanted to fill column Work_Days which was empty:

    CREATE TABLE [dbo].[Plan_Phase](
   

   [Id] [int] IDENTITY(1,1) NOT NULL, [Id_Plan] [int] NOT NULL, [Id_Phase] [int] NOT NULL, [Start_Date] [datetime] NULL, [End_Date] [datetime] NULL, [Work_Days] [int] NULL)

3. So in order to get "Work_Days" to later fill in my column just had to:

   SELECT Start_Date, End_Date, (DATEDIFF(dd, Start_Date, End_Date) + 1) -(DATEDIFF(wk, Start_Date, End_Date) * 2) -(SELECT COUNT() From Holiday Where Date >= Start_Date AND Date <= End_Date) -(CASE WHEN DATENAME(dw, Start_Date) = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN DATENAME(dw, End_Date) = 'Saturday' THEN 1 ELSE 0 END) -(CASE WHEN (SELECT COUNT() From Holiday Where Start_Date = Date) > 0 THEN 1 ELSE 0 END) -(CASE WHEN (SELECT COUNT(*) From Holiday Where End_Date = Date) > 0 THEN 1 ELSE 0 END) AS Work_Days from Plan_Phase

Hope that I could help.

Cheers

Here is a version that works well (I think). Holiday table contains Holiday_date columns that contains holidays your company observe.

DECLARE @RAWDAYS INT

   SELECT @RAWDAYS =  DATEDIFF(day, @StartDate, @EndDate )--+1
                    -( 2 * DATEDIFF( week, @StartDate, @EndDate ) )
                    + CASE WHEN DATENAME(dw, @StartDate) = 'Saturday' THEN 1 ELSE 0 END
                    - CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END 
					 
   SELECT  @RAWDAYS - COUNT(*) 
     FROM HOLIDAY NumberOfBusinessDays
    WHERE [Holiday_Date] BETWEEN @StartDate+1 AND @EndDate 

This is basically CMS's answer without the reliance on a particular language setting. And since we're shooting for generic, that means it should work for all @@datefirst settings as well.

datediff(day, <start>, <end>) + 1 - datediff(week, <start>, <end>) * 2
    /* if start is a Sunday, adjust by -1 */
  + case when datepart(weekday, <start>) = 8 - @@datefirst then -1 else 0 end
    /* if end is a Saturday, adjust by -1 */
  + case when datepart(weekday, <end>) = (13 - @@datefirst) % 7 + 1 then -1 else 0 end

datediff(week, ...) always uses a Saturday-to-Sunday boundary for weeks, so that expression is deterministic and doesn't need to be modified (as long as our definition of weekdays is consistently Monday through Friday.) Day numbering does vary according to the @@datefirst setting and the modified calculations handle this correction with the small complication of some modular arithmetic.

A cleaner way to deal with the Saturday/Sunday thing is to translate the dates prior to extracting a day of week value. After shifting, the values will be back in line with a fixed (and probably more familiar) numbering that starts with 1 on Sunday and ends with 7 on Saturday.

datediff(day, <start>, <end>) + 1 - datediff(week, <start>, <end>) * 2
  + case when datepart(weekday, dateadd(day, @@datefirst, <start>)) = 1 then -1 else 0 end
  + case when datepart(weekday, dateadd(day, @@datefirst, <end>))   = 7 then -1 else 0 end

I've tracked this form of the solution back at least as far as 2002 and an Itzik Ben-Gan article. (https://technet.microsoft.com/en-us/library/aa175781(v=sql.80).aspx) Though it needed a small tweak since newer date types don't allow date arithmetic, it is otherwise identical.

EDIT: I added back the +1 that had somehow been left off. It's also worth noting that this method always counts the start and end days. It also assumes that the end date is on or after the start date.

I know this is an old question but I needed a formula for workdays excluding the start date since I have several items and need the days to accumulate correctly.

None of the non-iterative answers worked for me.

I used a defintion like > Number of times midnight to monday, tuesday, wednesday, thursday and friday is passed

(others might count midnight to saturday instead of monday)

I ended up with this formula

SELECT DATEDIFF(day, @StartDate, @EndDate) /* all midnights passed */
     - DATEDIFF(week, @StartDate, @EndDate) /* remove sunday midnights */
     - DATEDIFF(week, DATEADD(day, 1, @StartDate), DATEADD(day, 1, @EndDate)) /* remove saturday midnights */

None of the functions above work for the same week or deal with holidays. I wrote this:

create FUNCTION [dbo].[ShiftHolidayToWorkday](@date date)
RETURNS date
AS
BEGIN
	IF DATENAME( dw, @Date ) = 'Saturday'
		SET @Date = DATEADD(day, - 1, @Date)

	ELSE IF DATENAME( dw, @Date ) = 'Sunday'
		SET @Date = DATEADD(day, 1, @Date)

	RETURN @date
END
GO

create FUNCTION [dbo].[GetHoliday](@date date)
RETURNS varchar(50)
AS
BEGIN
	declare @s varchar(50)

	SELECT @s = CASE
		WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]  ) + '-01-01') = @date THEN 'New Year'
		WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]+1) + '-01-01') = @date THEN 'New Year'
		WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]  ) + '-07-04') = @date THEN 'Independence Day'
		WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]  ) + '-12-25') = @date THEN 'Christmas Day'
		--WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]) + '-12-31') = @date THEN 'New Years Eve'
		--WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]) + '-11-11') = @date THEN 'Veteran''s Day'

		WHEN [Month] = 1  AND [DayOfMonth] BETWEEN 15 AND 21 AND [DayName] = 'Monday' THEN 'Martin Luther King Day'
		WHEN [Month] = 5  AND [DayOfMonth] >= 25             AND [DayName] = 'Monday' THEN 'Memorial Day'
		WHEN [Month] = 9  AND [DayOfMonth] <= 7              AND [DayName] = 'Monday' THEN 'Labor Day'
		WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 22 AND 28 AND [DayName] = 'Thursday' THEN 'Thanksgiving Day'
		WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 23 AND 29 AND [DayName] = 'Friday' THEN 'Day After Thanksgiving'
		ELSE NULL END
	FROM (
		SELECT
			[Year] = YEAR(@date),
			[Month] = MONTH(@date),
			[DayOfMonth] = DAY(@date),
			[DayName]   = DATENAME(weekday,@date)
	) c

	RETURN @s
END
GO

create FUNCTION [dbo].GetHolidays(@year int)
RETURNS TABLE 
AS
RETURN (  
    select dt, dbo.GetHoliday(dt) as Holiday
    from (
        select dateadd(day, number, convert(varchar,@year) + '-01-01') dt
        from master..spt_values 
        where type='p' 
        ) d
    where year(dt) = @year and dbo.GetHoliday(dt) is not null
)

create proc UpdateHolidaysTable
as

if not exists(select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Holidays')
    create table Holidays(dt date primary key clustered, Holiday varchar(50))

declare @year int
set @year = 1990

while @year < year(GetDate()) + 20
begin
    insert into Holidays(dt, Holiday)
    select a.dt, a.Holiday
    from dbo.GetHolidays(@year) a
        left join Holidays b on b.dt = a.dt
    where b.dt is null

    set @year = @year + 1
end

create FUNCTION [dbo].[GetWorkDays](@StartDate DATE = NULL, @EndDate DATE = NULL)
RETURNS INT 
AS
BEGIN
	IF @StartDate IS NULL OR @EndDate IS NULL
		RETURN  0

	IF @StartDate >= @EndDate 
		RETURN  0

    DECLARE @Days int
    SET @Days = 0

	IF year(@StartDate) * 100 + datepart(week, @StartDate) = year(@EndDate) * 100 + datepart(week, @EndDate) 
		--same week
		select @Days = (DATEDIFF(dd, @StartDate, @EndDate))
	  - (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
	  - (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
	  - (select count(*) from Holidays where dt between @StartDate and @EndDate)
	ELSE
		--diff weeks
		select @Days = (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)
	  - (select count(*) from Holidays where dt between @StartDate and @EndDate)
 
	RETURN  @Days
END

Using a date table:

    DECLARE 
    	@StartDate date = '2014-01-01',
    	@EndDate date = '2014-01-31'; 
    SELECT 
    	COUNT(*) As NumberOfWeekDays
    FROM dbo.Calendar
    WHERE CalendarDate BETWEEN @StartDate AND @EndDate
      AND IsWorkDay = 1;

If you don't have that, you can use a numbers table:

    DECLARE 
	@StartDate datetime = '2014-01-01',
	@EndDate datetime = '2014-01-31'; 
    SELECT 
	SUM(CASE WHEN DATEPART(dw, DATEADD(dd, Number-1, @StartDate)) BETWEEN 2 AND 6 THEN 1 ELSE 0 END) As NumberOfWeekDays
    FROM dbo.Numbers
    WHERE Number <= DATEDIFF(dd, @StartDate, @EndDate) + 1 -- Number table starts at 1, we want a 0 base

They should both be fast and it takes out the ambiguity/complexity. The first option is the best but if you don't have a calendar table you can allways create a numbers table with a CTE.

DECLARE @StartDate datetime,@EndDate datetime

select @StartDate='3/2/2010', @EndDate='3/7/2010'

DECLARE @TotalDays INT,@WorkDays INT

DECLARE @ReducedDayswithEndDate INT

DECLARE @WeekPart INT

DECLARE @DatePart INT

SET @TotalDays= DATEDIFF(day, @StartDate, @EndDate) +1

SELECT @ReducedDayswithEndDate = CASE DATENAME(weekday, @EndDate)
	WHEN 'Saturday' THEN 1
	WHEN 'Sunday' THEN 2
	ELSE 0 END

SET @TotalDays=@TotalDays-@ReducedDayswithEndDate

SET @WeekPart=@TotalDays/7;

SET @DatePart=@TotalDays%7;

SET @WorkDays=(@WeekPart*5)+@DatePart

SELECT @WorkDays

CREATE FUNCTION x
(
	@StartDate DATETIME,
	@EndDate DATETIME
)
RETURNS INT
AS
BEGIN
	DECLARE @Teller INT

	SET @StartDate = DATEADD(dd,1,@StartDate)

	SET @Teller = 0
	IF DATEDIFF(dd,@StartDate,@EndDate) <= 0
	BEGIN
		SET @Teller = 0 
	END
	ELSE
	BEGIN
		WHILE
			DATEDIFF(dd,@StartDate,@EndDate) >= 0
		BEGIN
			IF DATEPART(dw,@StartDate) < 6
			BEGIN
				SET @Teller = @Teller + 1
			END
			SET @StartDate = DATEADD(dd,1,@StartDate)
		END
	END
	RETURN @Teller
END

I took the various examples here, but in my particular situation we have a @PromisedDate for delivery and a @ReceivedDate for the actual receipt of the item. When an item was received before the "PromisedDate" the calculations were not totaling correctly unless I ordered the dates passed into the function by calendar order. Not wanting to check the dates every time, I changed the function to handle this for me.

Create FUNCTION [dbo].[fnGetBusinessDays]
(
 @PromiseDate date,
 @ReceivedDate date
)
RETURNS integer
AS
BEGIN
 DECLARE @days integer

 SELECT @days = 
	Case when @PromiseDate > @ReceivedDate Then
		DATEDIFF(d,@PromiseDate,@ReceivedDate) + 
		ABS(DATEDIFF(wk,@PromiseDate,@ReceivedDate)) * 2 +
		CASE 
			WHEN DATENAME(dw, @PromiseDate) <> 'Saturday' AND DATENAME(dw, @ReceivedDate) = 'Saturday' THEN 1 
			WHEN DATENAME(dw, @PromiseDate) = 'Saturday' AND DATENAME(dw, @ReceivedDate) <> 'Saturday' THEN -1 
			ELSE 0
		END +
		(Select COUNT(*) FROM CompanyHolidays 
			WHERE HolidayDate BETWEEN @ReceivedDate AND @PromiseDate 
			AND DATENAME(dw, HolidayDate) <> 'Saturday' AND DATENAME(dw, HolidayDate) <> 'Sunday')
	Else
		DATEDIFF(d,@PromiseDate,@ReceivedDate)  -
		ABS(DATEDIFF(wk,@PromiseDate,@ReceivedDate)) * 2  -
			CASE 
				WHEN DATENAME(dw, @PromiseDate) <> 'Saturday' AND DATENAME(dw, @ReceivedDate) = 'Saturday' THEN 1 
				WHEN DATENAME(dw, @PromiseDate) = 'Saturday' AND DATENAME(dw, @ReceivedDate) <> 'Saturday' THEN -1 
				ELSE 0
			END -
		(Select COUNT(*) FROM CompanyHolidays 
			WHERE HolidayDate BETWEEN @PromiseDate and @ReceivedDate 
			AND DATENAME(dw, HolidayDate) <> 'Saturday' AND DATENAME(dw, HolidayDate) <> 'Sunday')
	End
	

 RETURN (@days)

END

If you need to add work days to a given date, you can create a function that depends on a calendar table, described below:

CREATE TABLE Calendar
(
  dt SMALLDATETIME PRIMARY KEY, 
  IsWorkDay BIT
);

--fill the rows with normal days, weekends and holidays.


create function AddWorkingDays (@initialDate smalldatetime, @numberOfDays int)
	returns smalldatetime as 

	begin
		declare @result smalldatetime
		set @result = 
		(
			select t.dt from
			(
				select dt, ROW_NUMBER() over (order by dt) as daysAhead from calendar 
				where dt > @initialDate
				and IsWorkDay = 1
				) t
			where t.daysAhead = @numberOfDays
		)
			
		return @result
	end
			

As with DATEDIFF, I do not consider the end date to be part of the interval. The number of (for example) Sundays between @StartDate and @EndDate is the number of Sundays between an "initial" Monday and the @EndDate minus the number of Sundays between this "initial" Monday and the @StartDate. Knowing this, we can calculate the number of workdays as follows:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2018/01/01'
SET @EndDate = '2019/01/01'

SELECT DATEDIFF(Day, @StartDate, @EndDate) -- Total Days
  - (DATEDIFF(Day, 0, @EndDate)/7 - DATEDIFF(Day, 0, @StartDate)/7) -- Sundays
  - (DATEDIFF(Day, -1, @EndDate)/7 - DATEDIFF(Day, -1, @StartDate)/7) -- Saturdays

Best regards!

That's working for me, in my country on Saturday and Sunday are non-working days.

For me is important the time of @StartDate and @EndDate.

CREATE FUNCTION [dbo].[fnGetCountWorkingBusinessDays]
(
    @StartDate as DATETIME,
    @EndDate as DATETIME
)
RETURNS INT
AS
BEGIN
    DECLARE @res int
    
SET @StartDate = CASE 
	WHEN DATENAME(dw, @StartDate) = 'Saturday' THEN DATEADD(dd, 2, DATEDIFF(dd, 0, @StartDate))
	WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN DATEADD(dd, 1, DATEDIFF(dd, 0, @StartDate))
	ELSE @StartDate END
	
SET @EndDate = CASE 
	WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN DATEADD(dd, 0, DATEDIFF(dd, 0, @EndDate))
	WHEN DATENAME(dw, @EndDate) = 'Sunday' THEN DATEADD(dd, -1, DATEDIFF(dd, 0, @EndDate))
	ELSE @EndDate END
	
	
SET @res =
	(DATEDIFF(hour, @StartDate, @EndDate) / 24)
  - (DATEDIFF(wk, @StartDate, @EndDate) * 2)
	
SET @res = CASE WHEN @res < 0 THEN 0 ELSE @res END

    RETURN @res
END

GO

Create function like:

CREATE FUNCTION dbo.fn_WorkDays(@StartDate DATETIME, @EndDate DATETIME= NULL )
RETURNS INT 
AS
BEGIN
       DECLARE @Days int
       SET @Days = 0

       IF @EndDate = NULL
              SET @EndDate = EOMONTH(@StartDate) --last date of the month

       WHILE DATEDIFF(dd,@StartDate,@EndDate) >= 0
       BEGIN
              IF DATENAME(dw, @StartDate) <> 'Saturday' 
                     and DATENAME(dw, @StartDate) <> 'Sunday' 
                     and Not ((Day(@StartDate) = 1 And Month(@StartDate) = 1)) --New Year's Day.
                     and Not ((Day(@StartDate) = 4 And Month(@StartDate) = 7)) --Independence Day.
              BEGIN
                     SET @Days = @Days + 1
              END

              SET @StartDate = DATEADD(dd,1,@StartDate)
       END

       RETURN  @Days
END

You can call the function like:

select dbo.fn_WorkDays('1/1/2016', '9/25/2016')

Or like:

select dbo.fn_WorkDays(StartDate, EndDate) 
from table1

Create Function dbo.DateDiff_WeekDays 
(
@StartDate	DateTime,
@EndDate	DateTime
)
Returns Int
As

Begin	

Declare	@Result	Int = 0
	
While	@StartDate <= @EndDate
Begin 
	If DateName(DW, @StartDate) not in ('Saturday','Sunday')
		Begin
			Set @Result = @Result +1
		End
		Set @StartDate = DateAdd(Day, +1, @StartDate)
End

Return @Result

End

I found the below TSQL a fairly elegant solution (I don't have permissions to run functions). I found the DATEDIFF ignores DATEFIRST and I wanted my first day of the week to be a Monday. I also wanted the first working day to be set a zero and if it falls on a weekend Monday will be a zero. This may help someone who has a slightly different requirement :)

It does not handle bank holidays

SET DATEFIRST 1
SELECT
,(DATEDIFF(DD,  [StartDate], [EndDate]))		
-(DATEDIFF(wk,  [StartDate], [EndDate]))		
-(DATEDIFF(wk, DATEADD(dd,-@@DATEFIRST,[StartDate]), DATEADD(dd,-@@DATEFIRST,[EndDate]))) AS [WorkingDays] 
FROM /*Your Table*/ 

One approach is to 'walk the dates' from start to finish in conjunction with a case expression which checks if the day is not a Saturday or a Sunday and flagging it(1 for weekday, 0 for weekend). And in the end just sum flags(it would be equal to the count of 1-flags as the other flag is 0) to give you the number of weekdays.

You can use a GetNums(startNumber,endNumber) type of utility function which generates a series of numbers for 'looping' from start date to end date. Refer http://tsql.solidq.com/SourceCodes/GetNums.txt for an implementation. The logic can also be extended to cater for holidays(say if you have a holidays table)

declare @date1 as datetime = '19900101'
declare @date2 as datetime = '19900120'

select  sum(case when DATENAME(DW,currentDate) not in ('Saturday', 'Sunday') then 1 else 0 end) as noOfWorkDays
from dbo.GetNums(0,DATEDIFF(day,@date1, @date2)-1) as Num
cross apply (select DATEADD(day,n,@date1)) as Dates(currentDate)

I borrowed some ideas from others to create my solution. I use inline code to ignore weekends and U.S. federal holidays. In my environment, EndDate may be null, but it will never precede StartDate.

CREATE FUNCTION dbo.ufn_CalculateBusinessDays(
@StartDate DATE,
@EndDate DATE = NULL)

RETURNS INT
AS

BEGIN
DECLARE @TotalBusinessDays INT = 0;
DECLARE @TestDate DATE = @StartDate;


IF @EndDate IS NULL
	RETURN NULL;

WHILE @TestDate < @EndDate
BEGIN
	DECLARE @Month INT = DATEPART(MM, @TestDate);
	DECLARE @Day INT = DATEPART(DD, @TestDate);
	DECLARE @DayOfWeek INT = DATEPART(WEEKDAY, @TestDate) - 1; --Monday = 1, Tuesday = 2, etc.
	DECLARE @DayOccurrence INT = (@Day - 1) / 7 + 1; --Nth day of month (3rd Monday, for example)

	--Increment business day counter if not a weekend or holiday
	SELECT @TotalBusinessDays += (
		SELECT CASE
			--Saturday OR Sunday
			WHEN @DayOfWeek IN (6,7) THEN 0
			--New Year's Day
			WHEN @Month = 1 AND @Day = 1 THEN 0
			--MLK Jr. Day
			WHEN @Month = 1 AND @DayOfWeek = 1 AND @DayOccurrence = 3 THEN 0
			--G. Washington's Birthday
			WHEN @Month = 2 AND @DayOfWeek = 1 AND @DayOccurrence = 3 THEN 0
			--Memorial Day
			WHEN @Month = 5 AND @DayOfWeek = 1 AND @Day BETWEEN 25 AND 31 THEN 0
			--Independence Day
			WHEN @Month = 7 AND @Day = 4 THEN 0
			--Labor Day
			WHEN @Month = 9 AND @DayOfWeek = 1 AND @DayOccurrence = 1 THEN 0
			--Columbus Day
			WHEN @Month = 10 AND @DayOfWeek = 1 AND @DayOccurrence = 2 THEN 0
			--Veterans Day
			WHEN @Month = 11 AND @Day = 11 THEN 0
			--Thanksgiving
			WHEN @Month = 11 AND @DayOfWeek = 4 AND @DayOccurrence = 4 THEN 0
			--Christmas
			WHEN @Month = 12 AND @Day = 25 THEN 0
			ELSE 1
			END AS Result);

	SET @TestDate = DATEADD(dd, 1, @TestDate);
END

RETURN @TotalBusinessDays;
END