I came upon the need to calculate the number of weekdays between two dates. SQL Server offers several utilities for calculating dates, but nothing for this specific purpose. After staring at a calendar for some time and going through several revisions, I came up with a solution that seems to work pretty well. I do rely on 1 helper UDF, but it’s a pretty standard function that can easily be used elsewhere. Please note that I’ve designed the main function to be language independent—that is, it does not depend on @@DATEFIRST to be set any particular way.

First, the helper function:

create function dbo.fn_MaxInt(@Num1 int, @Num2 int)
returns int
as
--Helper function
--Return the larger of two integers
begin
	return case when @Num1 > @Num2 then @Num1 else @Num2 end
end

Now, the one we really want:

create function dbo.fn_WeekdayDiff(@StartDate datetime, @EndDate datetime)
returns int
as
--Calculdate weekdays between two dates
begin
	--if @StartDate is AFTER @EndDate, swap them
	if @StartDate > @EndDate
	begin
		declare @TempDate datetime
		set @TempDate = @StartDate
		set @StartDate = @EndDate
		set @EndDate = @TempDate
	end
	
	return
		--number of weeks x 5 weekdays/week
		  (datediff(ww, @StartDate, @EndDate) * 5)
		--add weekdays left in current week
		+ case DATEPART(dw, @StartDate + @@DATEFIRST) when 1 then 5 else (7 - DATEPART(dw, @StartDate + @@DATEFIRST)) end
		--subtract weekdays after @EndDate
		- dbo.fn_MaxInt(6 - datepart(dw, @EndDate + @@datefirst), 0)
end

Finally, some test cases:

select
	  dbo.fn_WeekDayDiff('2008-11-04', '2008-12-25') [2008-11-04:2008-12-25]
	, dbo.fn_WeekDayDiff('2008-11-04', '2008-12-26') [2008-11-04:2008-12-26]
	, dbo.fn_WeekDayDiff('2008-11-04', '2008-12-27') [2008-11-04:2008-12-27]
	, dbo.fn_WeekDayDiff('2008-11-04', '2008-12-28') [2008-11-04:2008-12-28]
	, dbo.fn_WeekDayDiff('2008-11-04', '2008-12-29') [2008-11-04:2008-12-29]
	, dbo.fn_WeekDayDiff('2008-11-04', '2008-12-30') [2008-11-04:2008-12-30]
	, dbo.fn_WeekDayDiff('2008-11-04', '2008-11-06') [2008-11-04:2008-11-06]
	, dbo.fn_WeekDayDiff('2008-11-04', '2008-11-04') [2008-11-04:2008-11-04]
	, dbo.fn_WeekDayDiff('2008-11-06', '2008-11-04') [2008-11-06:2008-11-04]

And results:

2008-11-04:2008-12-25 2008-11-04:2008-12-26 2008-11-04:2008-12-27 2008-11-04:2008-12-28 2008-11-04:2008-12-29 2008-11-04:2008-12-30 2008-11-04:2008-11-06 2008-11-04:2008-11-04 2008-11-06:2008-11-04
--------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- --------------------- ---------------------
38                    39                    39                    39                    40                    41                    3                     1                     3