• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 932
  • Last Modified:

SQL Datediff time delimma

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.  

case
                              when datediff(hour, min(s.arriveDateTime), max(s.departDateTime)) > 6 then
                                    datediff(minute, min(s.arriveDateTime), max(s.departDateTime)) - 30
                              else
                                    datediff(minute, min(s.arriveDateTime), max(s.departDateTime))
                        end DutyMinutes,

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.

Thanks,
Jen
0
swtjen01
Asked:
swtjen01
1 Solution
 
mfhorizonCommented:
You query is correct,
When you are calculating back to hours do like this
Take Non-Fractional part of "605 divided by 60" which is 10 (hours)
Take remainder part of "605 divided by 60" which is .083.
Multily remainder.083 with 60 and get round value 5 (minutes.)

it's just mathematical.
0
 
BrandonGalderisiCommented:
5 minutes is .083 hours.  If you want the value to be in hours:minutes, then try this.

Below there is setup with temporary data, but the main part to look at is lines 1 and 3.

Minutes from start to finish:
select datediff(minute, arriveDateTime, departDateTime) as shiftminutes

Add the number of minutes worked (-30 if over 6 hours) to Jan 1, 1900 and then extract the time with format code 108.
convert(varchar(8),dateadd(n, shiftminutes+case when shiftminutes > 360 then -30 else 0 end, 0),108)



 
select convert(varchar(8),dateadd(n, shiftminutes+case when shiftminutes > 360 then -30 else 0 end, 0),108)
from
(select datediff(minute, arriveDateTime, departDateTime) as shiftminutes
 
/*
This is just sample data to query from
*/
from (select getdate() as arrivedatetime, dateadd(hh, 7, getdate()) as departdatetime
union all select '2009-04-10 7:20', '2009-04-10 17:55'
) s
)d

Open in new window

0
 
swtjen01Author Commented:
It took me a bit to figure it and I had to adapt it for my query out but this was exactly what I needed and it works perfectly.  Thank you so much
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now