I have written a function to check if a date value is a working day or not (used for an employee leave application, working out days absent, so should be similar).  The function returns 1 if the date is a weekend, 2 if it is a Bank Holiday (see below) and 0 if it is a working day.  It can easily be altered to account for alternative business rules, and works for any setting of @@datefirst.

create function dbo.WorkingDay (@InputDate datetime)

returns tinyint

as

begin

declare @Sunday tinyint, @Saturday tinyint, @Year smallint

select @Year = datepart(yy,@InputDate)

select @Sunday = 8 - @@datefirst

select @Saturday = ( (@Sunday+5) % 7) + 1

if (datepart(dw,@InputDate) = @Sunday) or (datepart(dw,@InputDate) = @Saturday)

 return(1)

if @InputDate in (select * from dbo.BankHolidays(@Year))

 return(2)

return(0)

end

go

The function BankHolidays returns a table with all Bank Holiday dates for the given year.  Bank Holidays are UK public holidays - if anyone wants the script to calculate it let me know and I'll post.  To calculate Good Friday and Easter Monday holidays, it uses a function to calculate Easter between 1900 and 2099 using the Carter algorithm, which I assume is the same for US.