?
Solved

DateDiff for Accurate Minutes

Posted on 2005-03-23
24
Medium Priority
?
4,175 Views
Last Modified: 2007-12-19
Hi
Signin    SignOut
6:30      12:00

we can see the different is 5 hours and 10 Minutes

here is the Query

Select
      EmployeeID,  Sum(DateDiff(mi, SignIN, SIGNOUT))/60
From
      TimeClock
Group by EmployeeID


but it does not work, for some employees it return 5.70 or whatever which does not make sense, I want it to return the Accurate Time (Hour and Minute Format)

Thanks
0
Comment
Question by:khansoul
[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
  • 10
  • 8
  • 4
  • +1
24 Comments
 
LVL 18

Accepted Solution

by:
mdougan earned 600 total points
ID: 13614889
Hi khansoul,

Well, I see the difference is 5 hours and 30 minutes  ;)

But, use the modulo operator if you want the remainder after dividing out the hour portions

declare @SignIN smalldatetime
declare @SignOut smalldatetime

set @signIN = '06:30'
set @signOUT = '12:00'


PRINT CONVERT(VARCHAR(50), (DateDiff(mi, @SignIN, @SIGNOUT))/60) + ' Hours AND ' + CONVERT(VARCHAR(50), DateDiff(mi, @SignIN, @SIGNOUT) % 60) + ' Minutes'



Cheers!
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13614931
Select
     EmployeeID,  dbo.udf_ConvertMinutes(Sum(DateDiff(minute, SignIN, SIGNOUT)))
From
     TimeClock
Group by EmployeeID

CREATE FUNCTION dbo.udf_ConvertMinutes (@minutes int)
RETURNS varchar(20)
AS
BEGIN
DECLARE @return varchar(20)
SET @return = Cast(Floor(@minutes / 60) as varchar) + ':'
IF @minutes % 60 < 10
     SET @minutes = @minutes + '0'
SET @minutes = @minutes + Cast(@minutes % 60 as varchar)
RETURN @minutes
END
0
 
LVL 18

Expert Comment

by:mdougan
ID: 13614968
khansoul,

Another thought.... since you're trying to sum these up, you should sum up the minute totals, then when you want to display the total, you'd do a date add


Select
     EmployeeID,  Sum(DateDiff(mi, SignIN, SIGNOUT)) AS my_diff
into #temp
From
     TimeClock
Group by EmployeeID

Select EmployeeID, CONVERT(VARCHAR(8), DATEADD(mi, my_diff, '1950-01-01 00:00:00'), 108)

The dateAdd is going to add the sum of the minutes to midnight, so if the total minutes was 593 that would equal 9 hours and 53 minutes, then your date would end up looking like '1950-01-01 09:53:00', and by using the Convert function with the format of 108, you'll only display the time part of the date field.
0
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
LVL 18

Expert Comment

by:mdougan
ID: 13614974
khansoul,

That should have read

Select EmployeeID, CONVERT(VARCHAR(8), DATEADD(mi, my_diff, '1950-01-01 00:00:00'), 108)
from #temp
0
 

Author Comment

by:khansoul
ID: 13614980
So i got the concept of using Convert Function here (because we are concatenating).
so if we just divide with 60 it will not return the Correct Value.
and I dont understand how come it works fine and return correct value with % operator.

Explaination will be helpful.
Thanks
0
 

Author Comment

by:khansoul
ID: 13615041
Select
      EmployeeID,  convert(nvarchar(20), Sum(DateDiff(mi, SignIN, SIGNOUT)/60)) + ':' + Convert(nvarchar(20), Sum(DateDiff(mi, SignIN, SignOut)%60))
From
      TimeClock
Group by EmployeeID


Returning

EmployeeID     Time
1234          10:0
5678            26:85

Whats wrong now?
0
 
LVL 9

Expert Comment

by:sudheeshthegreat
ID: 13615050
45/60 = 0.75
45%60 = 45 (% gets the remainder)
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13615054
>so if we just divide with 60 it will not return the Correct Value.

It is returning the correct value it's just not the value you want.  330 / 60 = 5.5 but since both  values are integers it will return the integer value of 5.  That gets you your hours and the % (modulus) operator gets you the minutes
0
 
LVL 18

Expert Comment

by:mdougan
ID: 13615075
khansoul,

Well, there are two operations going on there... the first is dividing the total minutes (in your case 33) by 60 and the result is 5, that is because you are using a literal with no decimal positions, so, SQL Server is making the result an integer data type.  If you were to divide by 60.0 then the result would be 5.5

Since I wanted to seperate out the hours from the minutes in my example, it was OK that it did that converstion to Int, effectively truncating the decimal positions.  Then, I used the modulo operator  %  which is not really the division operator, what this operator does is divide by the number, but then return the remainder of the division, which in your case is 30 minutes.  If you're OK with decimal representation of hours  like  5.75 hrs then just use a 60.0 literal instead of 60
0
 
LVL 18

Expert Comment

by:mdougan
ID: 13615089
khansoul,

Like I said, stop trying to sum the hours and minutes seperately... just sum the minutes and then display the sum later:

Select
     EmployeeID,  Sum(DateDiff(mi, SignIN, SIGNOUT)) AS my_diff
into #temp
From
     TimeClock
Group by EmployeeID

Select EmployeeID, CONVERT(VARCHAR(8), DATEADD(mi, my_diff, '1950-01-01 00:00:00'), 108)
from #temp
0
 

Author Comment

by:khansoul
ID: 13615108
so I have to use the TEMP Table?
I dont have any solution within Query?
All I want to return is 1 week work for all employees:)
0
 
LVL 9

Expert Comment

by:sudheeshthegreat
ID: 13615160
how about this:
Select
     EmployeeID,  Sum(DateDiff(mi, SignIN, SIGNOUT))/60 + ':' + Sum(DateDiff(mi, SignIN, SIGNOUT))%60 AS hhmm
From
     TimeClock
Group by EmployeeID
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13615234
The only issue with sudheesh's solution is that you will get results like

5:1 instead of 5:01
0
 
LVL 18

Expert Comment

by:mdougan
ID: 13615296
khansoul,

Yea, I just tried your solution khansoul, and it did correctly sum and display the hours and minutes... since I don't have your table, I created a temp table and filled it... but when you asked, what is wrong now... nothing is wrong, your sum just totaled up the number of hours to 26.  If you wanted to display days instead, then you'll have to divide the hours by 24.

create table #temp (EmployeeID int, signIn smalldatetime, signOut smalldatetime)

insert into #temp (EmployeeID, signIn, signOut) VALUES (1, '1950-01-01 06:30', '1950-01-01 12:00')
insert into #temp (EmployeeID, signIn, signOut) VALUES (1, '1950-01-02 05:00', '1950-01-02 07:15')
insert into #temp (EmployeeID, signIn, signOut) VALUES (2, '1950-01-01 06:00', '1950-01-01 10:00')

Select
     EmployeeID,  convert(nvarchar(20), Sum(DateDiff(mi, SignIN, SIGNOUT)/60)) + ':' + Convert(nvarchar(20), Sum(DateDiff(mi, SignIN, SignOut)%60))
From
     #temp
Group by EmployeeID

drop table #temp
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13615339
Give the function I posted earlier a shot...Select
     EmployeeID,  dbo.udf_ConvertMinutes(Sum(DateDiff(minute, SignIN, SIGNOUT)))
From
     TimeClock
Group by EmployeeID

CREATE FUNCTION dbo.udf_ConvertMinutes (@minutes int)
RETURNS varchar(20)
AS
BEGIN
DECLARE @return varchar(20)
SET @return = Cast(@minutes / 60 as varchar) + ':'
IF @minutes % 60 < 10
     SET @minutes = @minutes + '0'
SET @minutes = @minutes + Cast(@minutes % 60 as varchar)
RETURN @minutes
END
0
 
LVL 18

Expert Comment

by:mdougan
ID: 13615342
khansoul,


OH, wait, I see, your minutes are not rolling over... OK, let's try this:

Select EmployeeID, CONVERT(VARCHAR(8), DATEADD(mi, my_diff, '1950-01-01 00:00:00'), 108)
FROM (
Select
     EmployeeID,  Sum(DateDiff(mi, SignIN, SIGNOUT)) AS my_diff
From
     TimeClock
Group by EmployeeID
) A
0
 

Author Comment

by:khansoul
ID: 13615344
No
EmployeeID     Time
1234         10:0
5678           26:85

26 is Fine what about 85?  Range should be within 0 - 60 Minutes, 85 Minutes is out of Range,
0
 

Author Comment

by:khansoul
ID: 13615455
mdougan  no that return Wrong values

1234      10:00:00
5678      03:25:00

0
 

Author Comment

by:khansoul
ID: 13615496
I have all the Minutes Correctly JEEEZ its just so hard to convert it into Correct Hours and Minutes.
pulling my Hair.......
0
 
LVL 18

Expert Comment

by:mdougan
ID: 13615520
khansoul,

Yea, the problem with that last bit of code is that it doesn't take into account the days different... so, you can do it using the basic structure that I was showing you, were you do the sum in the inner select statement, but then you have to format the output using your divide code:

Select EmployeeID,
    CONVERT(VARCHAR(2), my_diff / 60) + ':' +  CONVERT(VARCHAR(2), my_diff % 60)
FROM (
Select
     EmployeeID,  Sum(DateDiff(mi, SignIN, SIGNOUT)) AS my_diff
From
     TimeClock
Group by EmployeeID
) A
0
 
LVL 18

Expert Comment

by:mdougan
ID: 13615630
khansoul,

One last thing, you want to zero fill the values....

Select EmployeeID,
    REPLICATE('0', 2 - Len(CONVERT(VARCHAR(2), my_diff / 60))) + CONVERT(VARCHAR(2), my_diff / 60) + ':' +  REPLICATE('0', 2 - Len(CONVERT(VARCHAR(2), my_diff % 60))) + CONVERT(VARCHAR(2), my_diff % 60)

FROM (
Select
     EmployeeID,  Sum(DateDiff(mi, SignIN, SIGNOUT)) AS my_diff
From
     TimeClock
Group by EmployeeID
) A
0
 

Author Comment

by:khansoul
ID: 13615645
hi,
I was placing %60 at wrong spot

Select
       EmployeeID,  convert(nvarchar(20), Sum(DateDiff(mi, SignIN, SIGNOUT)/60)) + ':' +
      Convert(nvarchar(20), Sum(DateDiff(mi, SignIN, DATEADD(mi, -30, SignOut)))%60)
From
      TimeClock
Group by EmployeeID
From
      TimeClock
Group by EmployeeID



Seems like its working Now..
Thanks
0
 

Author Comment

by:khansoul
ID: 13615873
and this one with 30 Minute lunch Break:)

Select * from TimeClock

Select
       EmployeeID,  (convert(nvarchar(20), Sum(DateDiff(mi, SignIN, DATEADD(mi, -30, SIGNOUT)))/60) + ':' +
      Convert(nvarchar(20), Sum(DateDiff(mi, SignIN, DATEADD(mi, -30, SIGNOUT)))%60) ) Timer
From
      TimeClock
Group by EmployeeID
0
 
LVL 18

Expert Comment

by:mdougan
ID: 13616241
Ah... I didn't even notice that... yea, parenthesis can be tricky ;)  Glad you got it working!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

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

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

Join & Ask a Question