DateTime Subtraction Gives different result than you get with a calulator [Answered]RSS

• • Reply
  ronbsoft

  Member

  1 Points

  3 Posts

  DateTime Subtraction Gives different result than you get with a calulator

  May 16, 2016 01:58 AM|ronbsoft|LINK

  Hi All

  This one has me running around and around

  I am building a timing system as below. I need to show the result as "hh:mm:ss.fff"

  My problem is the fff (milliseconds) do not save in the Sql data base correctly.

  =======================================================

  Dim StartTime as Datetime

  Dim StopTime as DateTime

  Dim strRunTime as Timespan

  strRunTime = FinishTime.Subtract(StartTime)

  Then save all three.

  All three fields  in the Sql are DateTime

          Dim ts2 As TimeSpan = strRunTime
   
          Dim dt3 As New DateTime(1, 1, 1, ts2.Hours, ts2.Minutes, ts2.Seconds, ts2.Milliseconds)
   
          Dim mydataSave As String = "Data Source=RONS64BIT\SQLEXPRESS;Initial Catalog=ResultsData;Integrated Security=True;"
          Dim Sqlconn As SqlConnection = New SqlConnection(mydataSave)
   
  
   
          Dim query As String = "UPDATE LogData SET StopTime= @StopTime, tRunTime= @tRunTime, PenaltyCode= @PenaltyCode, Penalies= @Penalies WHERE logID='" & Myx & "'"
          Dim cmd As New SqlCommand(query, Sqlconn)
   
          cmd.Parameters.AddWithValue("@StopTime", DateTime.Parse(MyFinishTime.ToString("hh:mm:ss.fff")))
          cmd.Parameters.AddWithValue("@tRunTime", DateTime.Parse(dt3.ToString("hh:mm:ss.fff")))
          cmd.Parameters.AddWithValue("@PenaltyCode", strPenaltyCode)
          cmd.Parameters.AddWithValue("@Penalies", strTotalPenalty)
   
          cmd.Connection.Open()
          Try
              cmd.ExecuteNonQuery()
              '     MsgBox("Update all OK ")
          Catch ex As Exception
              Throw New Exception("Error " + ex.Message)
          End Try

  so the Results field is saved with the dt3 

  Stored in the Database as

  StartTime    = 2016-05-16 11:01:48.017

  Finish Time = 2016-05-16 11:01:51.290

  Results        =  2016-05-16 00:00:03.260

  so if you subtract 48.017 from the 51.290 you get 3.273 not the 3.260 as save in the database

  This is a good one for the maths people

  Hope someone can help

  Regards

  Ron B

• • Reply
  Junfeng Dai

  Member

  420 Points

  137 Posts

  Re: DateTime Subtraction Gives different result than you get with a calulator

  May 17, 2016 08:32 AM|Junfeng Dai|LINK

  Hi ronbsoft,

  so if you subtract 48.017 from the 51.290 you get 3.273 not the 3.260 as save in the database

  According to your description, as far as I know that SQL Server datetime has a precision of 1/300 of a second.

  If you need that millisecond accuracy, there's no pleasant way around it, but you could store the value in custom number fields and rebuild it every time you fetch the value, or to store it as a string of a known format. You can then (optionally) store an 'approximate' date in the native date type for the sake of speed, but it introduces a conceptual complexity that often isn't wanted.

  For more information, click here to refer about Why is SQL Server losing a millisecond?

  Best regards,
  Junfeng Dai
• • Reply
  ronbsoft

  Member

  1 Points

  3 Posts

  Re: DateTime Subtraction Gives different result than you get with a calulator

  May 17, 2016 11:21 AM|ronbsoft|LINK

  Hi junfeng

  Thanks for your reply

  I think yo may be correct. However your link (here) at the buttom of your reply just returns me back to My Own post. Is that correct ?

  Regards

  ronbsoft

• • Reply
  Junfeng Dai

  Member

  420 Points

  137 Posts

  Re: DateTime Subtraction Gives different result than you get with a calulator

  May 27, 2016 09:31 AM|Junfeng Dai|LINK

  Hi ronbsoft,

  However your link (here) at the buttom of your reply just returns me back to My Own post. Is that correct ?

  First I need say sorry for providing broken link, I have corrected it, and you could refer this link:

  http://stackoverflow.com/questions/715432/why-is-sql-server-losing-a-millisecond

  Thanks for your understanding.

  Best regards,
  Junfeng Dai