calculate duration within and after office hours

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

General SQL Server Forums

New to SQL Server Programming

calculate duration within and after office hours

Author

Topic

Dabuskol
Starting Member

4 Posts

Posted - 2012-07-27 : 14:35:35

Need help please.
1st scenario:
How can I calculate the duration within business hours.
BH: 09:00 to 17:00 daily.

Start Date : 2012-04-22 15:30
Duration in seconds: 14,400 seconds
result work time is only: 1h:30mins or 5400 seconds

2nd scenario:
How can I calculate the duration after business hours.
BH: 09:00 to 17:00 daily.

Start Date : 2012-04-25 18:00
Duration in seconds: 57600 seconds
result work time is only: 1h or 3600 seconds
it only count 1 hr of work which is past 9am the next day.

Thanks
Dabuskol

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 14:44:11

see
http://visakhm.blogspot.com/2010/03/calculating-business-hours.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dabuskol
Starting Member

4 Posts

Posted - 2012-07-27 : 15:14:40

sir i dont have end date, does it mean need to add
createdate and duration to have the end date?

int createseconds = dateadd(second,0,createdate)
enddate =createseconds + duation
enddate = enddate / 3600

thanks
dabuskol

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 15:21:29

quote:
Originally posted by Dabuskol

sir i dont have end date, does it mean need to add
createdate and duration to have the end date?

int createseconds = dateadd(second,0,createdate)
enddate =createseconds + duation
enddate = enddate / 3600

thanks
dabuskol

then derive out enddate using duration and pass it to function

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dabuskol
Starting Member

4 Posts

Posted - 2012-07-31 : 01:47:51

Can I still ask clarification?
The statement below should only give me 2 hours and 30 mins. result since the startdate is after office hour.
The result it gave is 9 hours.
calculate from function: Startdate - BusinessEndtime

select dbo.BusinessHoursTest('2012-01-01 17:11:24.000','2012-01-02 10:30:38.000','08:00','17:00')

Regards
Dabsukol

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 10:21:44

quote:
Originally posted by Dabuskol

Can I still ask clarification?
The statement below should only give me 2 hours and 30 mins. result since the startdate is after office hour.
The result it gave is 9 hours.
calculate from function: Startdate - BusinessEndtime

select dbo.BusinessHoursTest('2012-01-01 17:11:24.000','2012-01-02 10:30:38.000','08:00','17:00')

Regards
Dabsukol

can you try now. should work now...have done a small fix

also please keep in mind that it returns result rounded to next full hr ie as 3 hours

if you want result in minutes you need to replace last part from hours to minutes for totaltime

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources