DateDiff Calculation - odd results
Question posted by: Jennifer
(Guest)
on
September 26th, 2008 04:25 PM
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
3
Answers Posted
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
<Scrabble.Devotee@gmail.comwrote:
Quote:
Originally Posted by
>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
>
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
<Scrabble.Devotee@gmail.comwrote:
Quote:
Originally Posted by
>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
>
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
|
|
|
What is Bytes?
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 197,015 network members.
Top Community Contributors
|
