Adding Business Hours - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Adding Business Hours

Author

Topic

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-08-17 : 13:33:46

Hi Everyone,
I'm wondering if anyone has every had to add business hours to the current date. I'm working on a project right now where I need to add 4 business hours to the request date and have that be the due date.

When I first got this project in I didn't think anything of it, just add 4 hours to current date, but what if the current date is today at 4:00pm. This request really shouldn't be due until tomorrow at 11:00 am since our business day ends at 5:00pm.

Does that make sense? I'm just looking for some pointers or a place to start. What happens if the request comes in at 4:00 pm on Friday. Then it shouldn't be due until 11:00 on Monday morning.

Thoughts?

Thanks,
Ryan

Ryan Everhart
SBC

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-17 : 14:05:42

This should work for you... its exact though


Declare @busHrBegin datetime,
	@busHrEnd datetime

Set @busHrBegin = dateadd(hh,8,convert(datetime,'1/1/2000'))
Set @busHrEnd = dateadd(hh,17,convert(datetime,'1/1/2000'))

Select @busHrBegin, @busHrEnd

Declare @checkDate datetime,
	@hourLag int

Set @hourLag = 4
Set @checkDate = getdate()

Select 
	CheckDate,
	dueDate = case
			when dueDate between (@busHrBegin + datediff(dd,'1/1/2000',checkDate)) and @busHrEnd + datediff(dd,'1/1/2000',checkDate) then dueDate
			else dateadd(hh,(24-datediff(hh,@busHrBegin,@busHrEnd)),dueDate) end 
From
	(Select CheckDate = @checkDate, dueDate = dateadd(hh,@hourLag,@checkDate)) as A

Corey

rme8494
Yak Posting Veteran

98 Posts

Posted - 2004-08-17 : 14:14:03

Corey,
That is a great start, thank you! I will change what I was working with to fit your code. Then I just need to figure out the weekends!

Ryan

Ryan Everhart
SBC

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-17 : 14:24:19

w/ Weekends.... i think:


Declare @busHrBegin datetime,
	@busHrEnd datetime

Set @busHrBegin = dateadd(hh,8,convert(datetime,'1/1/2000'))
Set @busHrEnd = dateadd(hh,17,convert(datetime,'1/1/2000'))

Select @busHrBegin, @busHrEnd

Declare @checkDate datetime,
	@hourLag int

Set @hourLag = 4
Set @checkDate = getdate()+3

--Assuming the week starts on monday and ends on friday

Select
	CheckDate,
	dueDate = case when datepart(dw,dueDate) in (7,1) then dueDate + (2-(datepart(dw,dueDate)%7)) else dueDate end
From
	(
	Select 
		CheckDate,
		dueDate = case
				when dueDate between (@busHrBegin + datediff(dd,'1/1/2000',checkDate)) and @busHrEnd + datediff(dd,'1/1/2000',checkDate) then dueDate
				else dateadd(hh,(24-datediff(hh,@busHrBegin,@busHrEnd)),dueDate) end
	From
		(Select CheckDate = @checkDate, dueDate = dateadd(hh,@hourLag,@checkDate)) as A
	) as B

Corey

SqlFriend
Starting Member

26 Posts

Posted - 2004-08-17 : 17:51:29

You might also watch out for holidays.. there is a good example in 'transact sql for guru's' in dealing with business days and weekends and holidays.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources