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

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(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
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