locked
DateTime Subtraction Gives different result than you get with a calulator RRS feed

  • Question

  • User-1370056497 posted

    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(111ts2.Hoursts2.Minutests2.Secondsts2.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(querySqlconn)
     
            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

    Monday, May 16, 2016 1:58 AM

Answers

  • User-1664007096 posted

    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?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 17, 2016 8:32 AM

All replies

  • User-1664007096 posted

    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?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 17, 2016 8:32 AM
  • User-1370056497 posted

    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

    Tuesday, May 17, 2016 11:21 AM
  • User-1664007096 posted

    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.

    Friday, May 27, 2016 9:31 AM