DateDiff Calculation - odd results

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Jennifer
    #1

    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


    Tags: None
    • Roy Harvey (SQL Server MVP)
      #2
      Re: DateDiff Calculation - odd results

      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.Devot ee@gmail.comwro te:
      >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
      >

        Comment

        • Roy Harvey (SQL Server MVP)
          #3
          Re: DateDiff Calculation - odd results

          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.Devot ee@gmail.comwro te:
          >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
          >

            Comment

            • Eric Isaacs
              #4
              Re: DateDiff Calculation - odd results

              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

                Comment

                Previous template Next
                Working...