5

I have a table containing the date in unix time and a separate milliseconds field. I now try to create a date from the two fields for later calculation (e.g. filtering on a time range). After adding the milliseconds to the date created via ...

 dateadd(S, [timestamp_s], '1970-01-01')

by adding another DATEADD ...

dateadd(MS, [timestamp_ms], dateadd(S, [timestamp_s], '1970-01-01')) eventdate

... and then output the date the milliseconds are sometimes one millisecond off. Out of curiousity I then tried to extract the milliseconds just to see what this gives and its again 1 millisecond off.

enter image description here

I think it has to do with internal floating point precision but I don't see any rule in the data. Sometimes each operation takes 1 MS off, sometimes the first will subtract 1 but the DATEPART will then add 1 again, etc.

As this might cause frustration with some of the users I would like to understand the behaviour and ideally find a solution to the problem. Thanks in advance.

0

1 Answer 1

12

This is due to a strange limitation1 in the accuracy of the datetime data type, as documented:

Accuracy | Rounded to increments of .000, .003, or .007 seconds

The solution would be to use datetime2, which provides better accuracy, as you can see in this dbfiddle demo:

Screenshot of results in dbfiddle showing the correct milliseconds when using datetime2

The return type of the DATEADD is dynamic based on what you send into it. So the important thing is to make sure you are passing datetime2 to the step where you add the milliseconds on.


1 Randolph West has a great blog series on how SQL Server data types are stored, including one on dates and times (How SQL Server stores data types: dates and times). That post has a useful comment from Data Platform MVP Jeff Moden:

The time portion of DATETIME is actually an integer count of the number of 1/300’s of a second that have passed since midnight. That translates to a 3.3 millisecond resolution but that level of resolution isn’t possible for the way the data is stored so 0.0 and 3.3 ms are rounded down to 0ms and 3ms and 6.6 milliseconds is rounded up to 7 milliseconds.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.