For accurate counts of weekdays, I HIGHLY recommend a calendar table. That way, you can include holidays, etc.

create table Calendar (

Date datetime primary key,

constraint CK_Date_NoTime

check (Date = cast(cast(cast(date as float) as int) as datetime)),

Workday bit not null,

Year as datepart(year, date),

Month as datepart(month, date),

Day as datepart(day, date),

WeekDay as datepart(weekday, date))

go

insert into dbo.Calendar (Date, Workday)

select dateadd(day, number, '1/1/2000'),

case

when datepart(weekday, dateadd(day, number, '1/1/2000')) between 2 and 6 then 1

else 0

end

from dbo.numbers -- A table of 10-thousand numbers

go

create index IDX_Calendar_MonthDay on dbo.Calendar(month, day)

go

update dbo.Calendar

set workday = 0

where month = 7 and day = 4

or ... -- fill in other holidays here

go

select count(*)

from dbo.Calendar

where workday = 1

and date between '5/20/2008' and '6/9/2008'

With a table like that, you can easily have SQL do things like calculate the 1st Monday in September, or the 4th Thursday in November, record these things as holidays, and calculate workdays between dates.

;with Thanksgiving (Row, Date, Workday) as

(select row_number() over (order by date), date, workday

from dbo.Calendar

where Month = 11

and WeekDay = 5)

update Thanksgiving

set workday = 0

where row = 4

You can also add more indexes to it, if you so desire.

Using a 10-thousand day calendar starting from 1 Jan 2000, will give you a table that goes into 2027, which should be enough to start with. As needed, add more, or create a job that runs on the first day of each year and adds another year worth of days (if you do that, make sure it adds enough to account for leap years).