Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 4194

# DateDiff for Accurate Minutes

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
khansoul
• 10
• 8
• 4
• +1
1 Solution

Commented:
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

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

Commented:
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

Commented:
khansoul,

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

Author Commented:
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.

Thanks
0

Author Commented:
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

Commented:
45/60 = 0.75
45%60 = 45 (% gets the remainder)
0

>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

Commented:
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

Commented:
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 Commented:
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

Commented:
Select
EmployeeID,  Sum(DateDiff(mi, SignIN, SIGNOUT))/60 + ':' + Sum(DateDiff(mi, SignIN, SIGNOUT))%60 AS hhmm
From
TimeClock
Group by EmployeeID
0

The only issue with sudheesh's solution is that you will get results like

0

Commented:
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

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

Commented:
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 Commented:
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 Commented:
mdougan  no that return Wrong values

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

0

Author Commented:
I have all the Minutes Correctly JEEEZ its just so hard to convert it into Correct Hours and Minutes.
pulling my Hair.......
0

Commented:
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

Commented:
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 Commented:
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 Commented:
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

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

## Featured Post

• 10
• 8
• 4
• +1
Tackle projects and never again get stuck behind a technical roadblock.