Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# SQL Datediff time delimma

Posted on 2009-04-10
Medium Priority
919 Views
Last Modified: 2012-06-27
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
Question by:swtjen01
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
3 Comments

LVL 5

Expert Comment

ID: 24116162
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

LVL 39

Accepted Solution

BrandonGalderisi earned 2000 total points
ID: 24116544
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
``````
0

Author Closing Comment

ID: 31568933
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
###### Suggested Courses
Course of the Month9 days, 4 hours left to enroll

#### 721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.