DateDiff Calculation - odd results

[Jennifer]

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

[Roy Harvey (SQL Server MVP)]

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
<Sc**************@gmail.comwrote:

>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

[Roy Harvey (SQL Server MVP)]

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
<Sc**************@gmail.comwrote:

>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

[Eric Isaacs]

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