If you prepopulate a "calendar" table with your weekend days and holidays, you can also do a simple query as shown in the following example. Please note that the great majority of the code which follows is actually the code used to populate a table variable which on our system is a permanent table . All the work of calculating business days is done in the last three lines. (The tally table is simply a table of numbers [N] from one to a million which I always create in any db. )

Having a calendar table also lets you do some cute things such as having a column that designates whether the holiday is for U.S., Canada, or both, which lets your function take a parameter to pass to your where clause to pick the appropriate holidays. You might also want an option to NOT include Saturdays as business days, or to add 1 to the datediff. Different clients count turn times in different ways.

-----------------------------------------------------------------------------------------------------------------

-- example

-----------------------------------------------------------------------------------------------------------------

declare @start datetime

declare @end datetime

declare @weekends_holidays table (offday datetime primary key, weekday int)

select @start = '7/1/2008',@end = '7/10/2008'

-- the following query populates the table variable with some weekend days and the 4th of July

insert into @weekends_holidays

select distinct @start+N-1,datepart(dw,@start+N-1)

from tally

where N <= datediff(dd,@start,@end)

and datepart(dw,@start+N-1) in (1,7)-- living for the weekend

union all

select '7/4/2008',datepart(dw,'7/4/2008') -- three cheers for the red, white, and blue

select * from @weekends_holidays

-------------------------------------------------------------------------

-- all of the above was just to set up the following query

-- in practice, @weekends_holidays would be a permanent table

-------------------------------------------------------------------------

select datediff(dd,@start,@end) - count(*) as businessDays

from @weekends_holidays

where offday between @start and @end