Last post May 27, 2016 09:31 AM by Junfeng Dai
May 16, 2016 01:58 AM|ronbsoft|LINK
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)
' MsgBox("Update all OK ")
Catch ex As Exception
Throw New Exception("Error " + ex.Message)
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
May 17, 2016 08:32 AM|Junfeng Dai|LINK
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?
May 17, 2016 11:21 AM|ronbsoft|LINK
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 ?
May 27, 2016 09:31 AM|Junfeng Dai|LINK
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:
Thanks for your understanding.