| Author |
 Topic  |
|
|
Dabuskol
Starting Member
4 Posts |
 Posted - 07/27/2012 : 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
India
47023 Posts |
|
|
Dabuskol
Starting Member
4 Posts |
Posted - 07/27/2012 : 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
India
47023 Posts |
Posted - 07/27/2012 : 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 - 07/31/2012 : 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
India
47023 Posts |
Posted - 07/31/2012 : 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/
|
|
|
| |
Topic |
|
calculate duration within and after office hours
