calculate business hours between two dates

Does anyone have any idea how I could calculate the business hours between two dates (M-F 9-5). Ideally it would also skip holidays, but I will take any help I can get.
Thanks,
RobCreate a table of sequential numbers in your database. This is very hand to have, and can help you solve a lot of date-span related problems:

CREATE TABLE [dbo].[sequential_numbers] ([value] [int] NOT NULL)
GO

Populate the table with sequential values, starting with zero:

DECLARE @.value int
Set @.value = 0
While @.value <= 8760
BEGIN
INSERT INTO sequential_numbers (value) SELECT @.value where not exists (select * from sequential_numbers where value = @.value)
SET @.value = @.value + 1
END
GO

--Then, assuming you have a table (Holidays) that stores non-business days:
SELECT count(*) * 8 BusinessHours
FROM sequential_numbers
LEFT OUTER JOIN Holidays on DateAdd(dd, value, @.StartDate) = Holidays.HolidayDate
WHERE DateAdd(dd, value, @.StartDate) <= @.EndDate
AND DateName(dw, DateAdd(dd, value, @.StartDate)) not in ('Saturday', 'Sunday')
AND Holidays.HolidayDate is null

blindman|||you can accomplish the same withouth creating a permanent table:

create function dbo.fn_CartesianProduct() returns table as
return (
select top 100 percent id = (a0.id + a1.id + a2.id + a3.id) from
(
select 0 id union select 1 union select 2 union
select 3 union select 4 union select 5 union
select 6 union select 7 union select 8 union select 9
) a0,
(
select 0 id union select 10 union select 20 union
select 30 union select 40 union select 50 union
select 60 union select 70 union select 80 union select 90
) a1,
(
select 0 id union select 100 union select 200 union
select 300 union select 400 union select 500 union
select 600 union select 700 union select 800 union select 900
) a2,
(
select 0 id union select 1000 union select 2000 union
select 3000 union select 4000 union select 5000 union
select 6000 union select 7000 union select 8000 union select 9000
) a3
order by 1
)
go

select * from dbo.fn_CartesianProduct()|||Cool code.

I've accomplished the same thing using a user-defined function that returns a table of sequential numbers between two values, but not using your algorithm , for databases where I was not allowed to create a table.

The sequential_numbers table has come in handy so many times, that I just put it in all my databases.

blindman|||the advantage that I have with this function is that I can do unlimited number of calculations without having to do any io, including generating a calendar, for example, from 01/01/1753 through 06/25/2277 (you just need to add another sub-query into the function above).|||The sequential_numbers table has come in handy so many times, that I just put it in all my databases.

extremely handy, yes

i call it the Integers table

see Finding all the dates between two dates (http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid474893_tax285649,00.html) (free registration may be required)

rudy
http://r937.com/|||figured i'd chime in to say thanks (esp. to blindman)...this thread def just made my life easier. and i wanted to bump it up since it is very useful. thanks everyone, i will certainly be frequenting this site from now on.