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
  • Last Modified:

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
Asked:
khansoul
  • 10
  • 8
  • 4
  • +1
1 Solution
 
mdouganCommented:
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
 
Brian CroweDatabase AdministratorCommented:
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
 
mdouganCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
mdouganCommented:
khansoul,

That should have read

Select EmployeeID, CONVERT(VARCHAR(8), DATEADD(mi, my_diff, '1950-01-01 00:00:00'), 108)
from #temp
0
 
khansoulAuthor 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.

Explaination will be helpful.
Thanks
0
 
khansoulAuthor 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
 
sudheeshthegreatCommented:
45/60 = 0.75
45%60 = 45 (% gets the remainder)
0
 
Brian CroweDatabase AdministratorCommented:
>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
 
mdouganCommented:
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
 
mdouganCommented:
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
 
khansoulAuthor 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
 
sudheeshthegreatCommented:
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
 
Brian CroweDatabase AdministratorCommented:
The only issue with sudheesh's solution is that you will get results like

5:1 instead of 5:01
0
 
mdouganCommented:
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
 
Brian CroweDatabase AdministratorCommented:
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
 
mdouganCommented:
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
 
khansoulAuthor 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
 
khansoulAuthor Commented:
mdougan  no that return Wrong values

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

0
 
khansoulAuthor Commented:
I have all the Minutes Correctly JEEEZ its just so hard to convert it into Correct Hours and Minutes.
pulling my Hair.......
0
 
mdouganCommented:
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
 
mdouganCommented:
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
 
khansoulAuthor 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
 
khansoulAuthor 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
 
mdouganCommented:
Ah... I didn't even notice that... yea, parenthesis can be tricky ;)  Glad you got it working!
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

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