By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

DateDiff Calculation - odd results

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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.