467,149 Members | 1,335 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,149 developers. It's quick & easy.

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
<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
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
<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
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.

Similar topics

8 posts views Thread by inamori | last post: by
4 posts views Thread by Paolo | last post: by
5 posts views Thread by mcbill20@yahoo.com | last post: by
6 posts views Thread by kevinjwilson@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.