Why does this cast to DATETIME2 contain extra granularity?-CodePudding

I am trying to cast the following DATETIME to DATETIME2:

DECLARE @x DATETIME = '2021-12-10 19:58:41.333'
SELECT CAST(@x AS DATETIME2)

The result is 2021-12-10 19:58:41.3333333. Why does the cast add 0.0003333s?

CodePudding user response：

Contrary to what some understand, datetime is not accurate to 1/1000th of a second, it is accurate to 1/300th of a second. This is why the last digit of a datetime is always 0, 3, or 7, as they represent 0/300th, 1/300th and 2/300th respectively of a second (3/300th would be 0 again).

For datetime2, however, the datatype can have a precision of 0 - 7. 0 meaning accurate to 1 second, and 7 being 100 nanoseconds or 1/10 microseconds.

When you convert a datetime to a datetime2 the fact that datetime is only accurate to 1/300th of a second is reflected in the converted value (Note that for older versions of SQL Server this is not true) and so will show the 1/300th to a the new degree of accuracy.

Let's take the value you have 2021-12-10 19:58:41.333. Here the .333 is infinite, it's more .3333333333~. This is because you can't represent 1/3rd (or 1/300th) accurately with a base 10 number. You then convert (CAST) your value to a datetime2. You omit the precision, so this defaults to a precision of 7. This gives you 2021-12-10T19:58:41.3333333, as the 1/300th is now displayed up to a precision of 7 (note that the above is accurate to 100 nanoseconds, not 100/3 nanoseconds).

Had you had a time like 2021-12-10 19:58:41.357 and converted it to a datetime2(4) you would have got 2021-12-10 19:58:41.3567. If you were to then convert that to a datetime2(7) you would get 2021-12-10 19:58:41.3567000 as the "1/300th-ness" accuracy was lost when it was converted to a datetime2(4).

As alluded to, in older versions of SQL Server (2014 prior if I recall correctly) the above is not true. In these older versions, the datetime value 2021-12-10 19:58:41.333 would be less accurately converted to the datetime2(7) value 2021-12-10 19:58:41.3330000. If you are on an older version and rely on this behaviour, you need to explicitly convert/cast your datetime value to a datetime2(3) to avoid a likely breaking change.

CodePudding user response：

This is actually due to the granularity of DATETIME. You will notice there are only 3 possible values for milliseconds in datetime: 0,333,or 667. This is because its granularity is one third of the second. When converted to datetime2, this expands the decimal digits to correctly represent the fraction.