TSQL Business Days

Exclude weekends.

CREATE FUNCTION [dbo].[BusinessDays]
(	
	@start	DATETIME, 
	@end	DATETIME
)
RETURNS INT
AS
BEGIN

	DECLARE	@wks			INT,
			@businessDays	INT, 
			@sdays			INT,
			@edays			INT,
			@startIndex		INT,
			@endIndex		INT

	SET @startIndex = DATEPART(dw, @start)
	SET @endIndex = DATEPART(dw, @end)
    SET @end = @end - 1
	SET @wks = DATEDIFF( WEEK, @start, @end) - 1
	SET @businessDays = @wks * 5
	
	IF DATEPART( dw, @start) = 7
		SET @sdays = 7 - DATEPART( dw, @start)
	ELSE
		SET @sdays = 7 - DATEPART( dw, @start) - 1
	
	IF DATEPART( dw, @end) = 7
		SET @edays = DATEPART( dw, @end) - 2
	ELSE
		SET @edays = DATEPART( dw, @end) - 1

	SET @businessDays = @businessDays + @sdays + @edays

	IF @startIndex <> 1 AND @startIndex <> 7
		SET @businessDays = @businessDays + 1

	IF @endIndex <> 1 AND @endIndex <> 7
		SET @businessDays = @businessDays + 1
		
	RETURN (@businessDays)
END
GO

SELECT [dbo].[BusinessDays]('2019-08-01','2019-08-12')
-- 8

Account for holidays.

CREATE FUNCTION [dbo].[ComputeBusinessDays]
(	
	@start	DATETIME, 
	@end	DATETIME
)
RETURNS INT
AS
BEGIN
  
DECLARE @days INT,
        @holidays INT
        
SELECT @days = dbo.BusinessDays(@start,@end)

SELECT 
	@holidays = COUNT(*) 
FROM 
	tblHoliday (NOLOCK)
WHERE 
	HolidayDate >= CONVERT(VARCHAR(10), @start, 101) 
	AND 
	HolidayDate <= CONVERT(VARCHAR(10), @end, 101) 
	AND 
	DATEPART(DW, HolidayDate) NOT IN (1,7)

RETURN @days - @holidays


END
GO