Get number of business days between dates

Hi. Is there a way to get the number of business days or hours between two dates? Also, is there a way to have it ignore weekends and U.S. holidays? Thank you.

dev:

The short answer to your question is yes, there is a compute the difference of two dates in business days. Give this article a look and consider using a calendar table.

Dave

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

|||There is nothing built in which does that. Besides everyone has different holidays federal, state, local, company?

The best way I have come up with is to create a table of non-business days, with a date field as the primary key, so you don't get holidays on Sat/Sun. And populate it for X years. If you are looking at over 10 years, this gets difficult, but not imposible.

Then you simply count the records between startdate and enddate and just subtract the count from the days.

|||The method described by Tom is the method that I have implemented most frequently; it is a good way to go.|||Thank you, Tom and Mugambo. Your suggestions worked.|||

i am using this script to get amount of business days

Code Snippet

set datefirst 1
declare @.sdate datetime
declare @.edate datetime

select @.sdate = '20070516' --for example, start date May, 16th
select @.edate='20070531' --end date May, 31st

select datediff(day, @.sdate, @.edate)+1-(
select (case datepart(dw, @.sdate)
when 7 then (datepart(ww, @.edate)-datepart(ww, @.sdate))*2-1
else (datepart(ww, @.edate)-datepart(ww, @.sdate))*2
end)+
(case datepart(dw, @.edate)
when 6 then 1
when 7 then 2
else 0
end)
)