467,149 Members | 1,335 Online

# DateDiff Calculation - odd results

 I've got a bit of a problem calculating the duration of hours between a clock-in time and a clock-out time. It's a fairly straight-forward process. I put the clock-ins, clock-outs in a temp table and then do an update on the table UPDATE #TempEmpHours SET Duration = DATEDIFF(mi, ClockIn, ClockOut)/60.0 The wacky results are below. Every other record has a negative value for the duration. Has anybody ever seen something like this? Yes, I am starting out with an empty temp table. I'm almost at my wit's end. Any ideas? Thanks, Jennifer ClockIn ClockOut Duration 8/23/08 3:00 PM 8/23/08 4:41 PM -18.2167 8/23/08 3:00 PM 8/23/08 6:02 PM 3.0333 8/23/08 3:00 PM 8/23/08 4:59 PM -17.9167 8/23/08 3:00 PM 8/23/08 7:20 PM 4.3333 8/23/08 3:00 PM 8/23/08 7:01 PM -15.8833 Sep 26 '08 #1
• viewed: 2357
Share:
3 Replies
 So far the only thing I have figure out is that the negative are all off by 1194 minutes. Very odd. Roy Harvey Beacon Falls, CT On Fri, 26 Sep 2008 08:22:55 -0700 (PDT), Jennifer I've got a bit of a problem calculating the duration of hours betweena clock-in time and a clock-out time. It's a fairly straight-forwardprocess. I put the clock-ins, clock-outs in a temp table and then doan update on the tableUPDATE #TempEmpHoursSET Duration = DATEDIFF(mi, ClockIn, ClockOut)/60.0The wacky results are below. Every other record has a negative valuefor the duration. Has anybody ever seen something like this? Yes, Iam starting out with an empty temp table. I'm almost at my wit'send. Any ideas?Thanks,JenniferClockIn ClockOut Duration8/23/08 3:00 PM 8/23/08 4:41 PM -18.21678/23/08 3:00 PM 8/23/08 6:02 PM 3.03338/23/08 3:00 PM 8/23/08 4:59 PM -17.91678/23/08 3:00 PM 8/23/08 7:20 PM 4.33338/23/08 3:00 PM 8/23/08 7:01 PM -15.8833 Sep 26 '08 #2
 Exactly what version of SQL Server are you using? Exactly what datatypes are ClockIn and ClockOut? Roy Harvey Beacon Falls, CT On Fri, 26 Sep 2008 08:22:55 -0700 (PDT), Jennifer I've got a bit of a problem calculating the duration of hours betweena clock-in time and a clock-out time. It's a fairly straight-forwardprocess. I put the clock-ins, clock-outs in a temp table and then doan update on the tableUPDATE #TempEmpHoursSET Duration = DATEDIFF(mi, ClockIn, ClockOut)/60.0The wacky results are below. Every other record has a negative valuefor the duration. Has anybody ever seen something like this? Yes, Iam starting out with an empty temp table. I'm almost at my wit'send. Any ideas?Thanks,JenniferClockIn ClockOut Duration8/23/08 3:00 PM 8/23/08 4:41 PM -18.21678/23/08 3:00 PM 8/23/08 6:02 PM 3.03338/23/08 3:00 PM 8/23/08 4:59 PM -17.91678/23/08 3:00 PM 8/23/08 7:20 PM 4.33338/23/08 3:00 PM 8/23/08 7:01 PM -15.8833 Sep 26 '08 #3
 I think its the datatypes too. The results I receive below work fine with your update statement in SQL Server 2005... CREATE TABLE #TempEmpHours ( ClockIn DATETIME, ClockOut DATETIME, Duration FLOAT ) INSERT INTO #TempEmpHours ( ClockIn, ClockOut ) SELECT '8/23/08 3:00 PM', '8/23/08 4:41 PM' UNION SELECT '8/23/08 3:00 PM', '8/23/08 6:02 PM' UNION SELECT '8/23/08 3:00 PM', '8/23/08 4:59 PM' UNION SELECT '8/23/08 3:00 PM', '8/23/08 7:20 PM' UNION SELECT '8/23/08 3:00 PM', '8/23/08 7:01 PM' UPDATE #TempEmpHours SET Duration = DATEDIFF(mi, ClockIn, ClockOut)/60.0 SELECT * FROM #TempEmpHours 2008-08-23 15:00:00.000 2008-08-23 16:41:00.000 1.683333 2008-08-23 15:00:00.000 2008-08-23 16:59:00.000 1.983333 2008-08-23 15:00:00.000 2008-08-23 18:02:00.000 3.033333 2008-08-23 15:00:00.000 2008-08-23 19:01:00.000 4.016666 2008-08-23 15:00:00.000 2008-08-23 19:20:00.000 4.333333 -Eric Isaacs Sep 29 '08 #4

### This discussion thread is closed

Replies have been disabled for this discussion.