DateDiff for Accurate Minutes

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

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.

khansoul,

That should have read

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

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

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?

45/60 = 0.75
45%60 = 45 (% gets the remainder)

>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

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

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

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

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

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

5:1 instead of 5:01

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

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

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

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,

mdougan  no that return Wrong values

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

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

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

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

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

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

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