1. Registered User
Join Date
Sep 2003
Posts
30

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,

Rob

2. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Create 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:
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

3. Registered User
Join Date
Sep 2003
Posts
522
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()

4. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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

5. Registered User
Join Date
Sep 2003
Posts
522
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).

6. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
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 (free registration may be required)

rudy
http://r937.com/

7. Registered User
Join Date
Jul 2003
Location
DC Metro
Posts
27
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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•