Couldn't get yours to work. Too many errors in the post. We're using this one. I wanted to see the difference. The table of holidays contains only holidays that fall on business days.
CREATE FUNCTION [dbo].[CalculateBusinessDays]
(
@StartDate DATE,
@EndDate DATE
)
RETURNS INTEGER
AS
BEGIN
IF @EndDate IS NULL
SELECT @EndDate = GETDATE()
RETURN CONVERT(INTEGER,
(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)
- ISNULL((SELECT COUNT(*) FROM tblHoliDays
WHERE @StartDate <= Holiday AND @EndDate >= Holiday), 0))
END