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?
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
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
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,
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?
Junfeng Dai
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
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,
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.
Junfeng Dai