DATEPART(MS,datetime) returns incorrect value in SQL Server 2016 databases

Question

I'm running SQL Server 2016, and recently changed the compatibility level of one of our databases to SQL Server 2016 (130).

After that, I noticed the DATEPART function returns the wrong millisecond value from a DATETIME data type, for any value ending in '7'. This was first noticed in a UDF which converts a datetime and smallint value (the latter containing the milliseconds) into a datetime2(3) value.

For example:

DECLARE @datetime_value DATETIME
SET @datetime_value = '2000-01-01 00:00:00.927'
SELECT DATEPART(MS,@datetime_value)

returns

926

I'm expecting 927. I've tried this on an SQL Server 2008 instance, and databases compatible with SQL Server 2014, and all these return the correct value.

Is this a known bug, and how can it be addressed? Casting the value as DATETIME2(3) before passing it to DATEPART function works, however this means updating any functions/procedures which use it.

Answer 1

This likely stems from a breaking change made back in SQL Server 2016: Database Engine: Breaking changes - SQL Server 2016

Under database compatibility level 130, implicit conversions from datetime to datetime2 data types show improved accuracy by accounting for the fractional milliseconds, resulting in different converted values. Use explicit casting to datetime2 datatype whenever a mixed comparison scenario between datetime and datetime2 datatypes exists. For more information, see this Microsoft Support Article

Prior to 2016 when a datetime was converted to a datetime2 the presented value of the datetime was used. In your case, this is 2000-01-01 00:00:00.927; so as a datetime2(7) this resulted in 2000-01-01 00:00:00.9270000.

As AlwaysLearning has mentioned, however, datetime is accurate to 1/300th of a second, so the datetime value 2000-01-01 00:00:00.927 is actually 2000-01-01 00:00:00.92666666666~. As a result in 2016+ the value as a datetime2(7) would be converted to the value 2000-01-01 00:00:00.9266667.

In SQL Server 2014, therefore, when you use MILLISECOND for the DATEPART you got the 7, as the value was first rounded up and then the value taken@: 92666666~ -> 927. On 2016 onwards, however, for recent versions the value is treated as 2000-01-01 00:00:00.92666666666~ and so you get 926.