SQL Millisecond Precision
I am storing the time in a DATETIME field in SQL, but when I try to delete anything less than that value, it actually removes that value. eg,
I have a value 2015-06-24 16:32:42. 243 in the table when I run the below query it removes the value even if it is not in it. Why?
DELETE FROM MyTable
WHERE MyDate <= '2015-06-24 16:32:42.242'
Is there a problem with accuracy?
source to share
Values in the obsolete type datetime
are only stored with an accuracy of 1 / 300th of a second.
ms allowable values lie in 0
, 3
, 7
anything else is rounded to the nearest one ( 5
equidistantly between 3
and 7
rounded to 7
, this is the only setting 2 ms).
So, .242
rounds up to .243
when implicitly casts todatetime
If you are on SQL Server 2008+ you can use datetime2(3)
ms for maximum precision.
Or just use
DELETE FROM MyTable WHERE MyDate < '2015-06-24 16:32:42.243'
source to share