Interesting bug with milliseconds and datetime on SQL Server 2016

The other day, I was messing around and ran code like the following on SQL Server 2016

DECLARE @ms int
DECLARE @datetime datetime = '2016-11-03T10:18:17.847'   --846 on SQL 2016
SET @ms = DATEPART(ms, @datetime)

SELECT  @ms
GO

DECLARE @ms int  
DECLARE @datetime datetime2 = '2016-11-03T10:18:17.847'  --847
SET @ms = DATEPART(ms, @datetime)

SELECT  @ms
GO

DECLARE @ms int
DECLARE @datetime time = '2016-11-03T10:18:17.847'   --847
SET @ms = DATEPART(ms, @datetime)

SELECT  @ms
GO

When I ran the code on SQL Server 2014 and SQL Server 2012, I got back 847 for all 3 queries. Running it on SQL Server 2016 returns 846 instead of 847 for the datetime data type.

Interesting, something must have changed. I then did a couple of searches on the internetz and .....


....I actually found this comment from Erland Sommarskog on the MS forums

Yes, this is an intended change.
datetime has a resolution of 3 1/3 ms and the exact value of what looks like 2016-09-27 18:03:03.297 is 2016-09-27 18:03:03.296666...
Previously this was not reflected in the internal represenation, but this was changed in SQL 2016.
It seems that you found the earlier thread about this. The poster there expressed concern about Entity Framework, but there was a suggestion from Erik Eskjov Jensen, which I am not able to evaluate myself. Does his suggestion help you?

From there I found that someone already filed a bug for this: https://connect.microsoft.com/SQLServer/feedback/details/3104723

And just 3 days ago, a comment was posted

Posted by Dan Guzman on 10/1/2016 at 9:04 PM
Yes, this is an intended change but has not yet been documented in the Breaking Changes topic in the SQL Server 2016 Books Online as of this writing.
The datetime value of '2016-09-27 18:03:03.297' is actually '2016-09-27 18:03:03.2966666...' because SQL Server internally uses 1/300 second interval units for the time portion. The infinitely repeating value is rounded to the fixed datetime precision of 3, resulting in the '2016-09-27 18:03:03.297' value. However, when the value is converted to a higher precision type, the repeating value is rounded to the target data type precision, which is '2016-09-27 18:03:03.2966667' in the case of datetime2(7).

This behavior is different than before SQL 2016, where the internal value was first rounded to a precision of 3, losing the available sub-millisecond value. As you observed, the conversion behavior may be controlled with the database compatibility level.

So there you have it, now you know the reason and this is indeed a breaking change.

So I guess it is a feature not a bug