SQL Datediff time delimma
Posted on 2009-04-10
I am trying to set up a statement that will calculate the hours correctly and deduct the break time if the employee has worked over 6 hours.
Here is my current statement that does return the correct minutes.
when datediff(hour, min(s.arriveDateTime), max(s.departDateTime)) > 6 then
datediff(minute, min(s.arriveDateTime), max(s.departDateTime)) - 30
datediff(minute, min(s.arriveDateTime), max(s.departDateTime))
This does return the correct number of minutes but here is my problem:
A person has a start time of 7:20 and a end time of 17:55&.which equates to 10 hours and 35 minutes.
The break logic says basically if they work over 6 hours then deduct -30 minutes.
So 10 hours and 35 minutes = 635 minutes minus the break would be 605 minutes&.now to turn that back into hours you think you would just divide by 60 but when you do the calculation is 10.08&.the correct time should show 10.05
I know this is in the way I am setting up the time. How can I do this to get the correct time to show.