This post is to get the word out about a breaking change to datetime conversion and comparison behavior in SQL Server 2016 and Azure SQL Database V12. This change hasn’t been documented as of this writing in the Breaking Changes to Database Engine Features in SQL Server 2016 topic in the SQL Server Books Online.
In short, conversion from datetime to a higher precision temporal data type (datetime2, datetimeoffset, or time) may yield a different, but more accurate, time value than in prior versions. Also, predicates involving datetime consider the full precision of raw datetime internal value instead of the time value rounded to the nearest millisecond. These changes in conversion and comparison behavior may affect existing applications and are not intuitive unless one understands the underlying datetime data type implementation.
Background
You may be aware that the accuracy of datetime is limited to 1/300 of a second. This is because values are internally an 8-byte structure consisting of 2 separate 32-bit integers, one with the number of day units since 1900-01-01 and the other with the number of 1/300 second interval units since midnight. The 1/300 second unit interval limits the time accuracy to 3.33333… milliseconds and the milliseconds value will be a repeating decimal when time interval units are not evenly divisible by 3. The raw decimal value is rounded to a scale of 3 in accordance with the fixed datetime precision of 3, resulting in a millisecond value of 0, 3, or 7 for all datetime values.
Pre-SQL Server 2016 Behavior
Before SQL Server 2016, conversion from datetime to another temporal type used the source datetime value after it was rounded to the nearest millisecond, which truncated repeating decimal fractional milliseconds. The rounded value was then rounded again according to the target type precision. When the target type precision was greater than 3, the time was extended to the target type precision with insignificant trailing zeros, resulting in zero for the sub-millisecond value.
DECLARE @DateTime datetime = '2016-01-01T00:00:00.007'; SELECT CAST(@DateTime AS datetime2(0)); --2016-01-01 00:00:00 SELECT CAST(@DateTime AS datetime2(1)); --2016-01-01 00:00:00.0 SELECT CAST(@DateTime AS datetime2(2)); --2016-01-01 00:00:00.01 SELECT CAST(@DateTime AS datetime2(3)); --2016-01-01 00:00:00.007 SELECT CAST(@DateTime AS datetime2(4)); --2016-01-01 00:00:00.0070 SELECT CAST(@DateTime AS datetime2(5)); --2016-01-01 00:00:00.00700 SELECT CAST(@DateTime AS datetime2(6)); --2016-01-01 00:00:00.007000 SELECT CAST(@DateTime AS datetime2(7)); --2016-01-01 00:00:00.0070000
Also, when datetime was compared to another temporal type, the rounded value was used. This script shows the result of the equality predicate is true after the datetime value is converted to datetime2.
--This script prints EQUAL predicate is true DECLARE @DateTime datetime = '2016-01-01T00:00:00.003'; DECLARE @DateTime2 datetime2(7) = @DateTime; IF @DateTime = @DateTime2 PRINT 'EQUAL predicate is true' ELSE PRINT 'EQUAL predicate is not true'; IF @DateTime < @DateTime2 PRINT 'LESS THAN predicate is true' ELSE PRINT 'LESS THAN predicate is not true'; IF @DateTime > @DateTime2 PRINT 'GREATER THAN predicate is true' ELSE PRINT 'GREATER THAN predicate is not true'; GO --This script prints EQUAL predicate is true DECLARE @DateTime datetime = '2016-01-01T00:00:00.007'; DECLARE @DateTime2 datetime2(7) = @DateTime; IF @DateTime = @DateTime2 PRINT 'EQUAL predicate is true' ELSE PRINT 'EQUAL predicate is not true'; IF @DateTime < @DateTime2 PRINT 'LESS THAN predicate is true' ELSE PRINT 'LESS THAN predicate is not true'; IF @DateTime > @DateTime2 PRINT 'GREATER THAN predicate is true' ELSE PRINT 'GREATER THAN predicate is not true'; GO
SQL Server 2016 Behavior Change
SQL Server 2016 and Azure SQL Database V12 use the raw datetime internal value without rounding during conversion to another temporal type. The value is rounded only once during conversion, to the target type precision. The end result will be the same as before SQL Server 2016 when the target type precision is 3 or less. However, the converted value will be different when the target type precision is greater than 3 and the internal time unit interval is not evenly divisible by 3 (i.e. rounded source datetime millisecond value is 3 or 7). Note the non-zero microseconds and nanoseconds in the script results below and that rounding is based on the target type precision rather than the source.
DECLARE @DateTime datetime = '2016-01-01T00:00:00.003'; SELECT CAST(@DateTime AS datetime2(0)); --2016-01-01 00:00:00 SELECT CAST(@DateTime AS datetime2(1)); --2016-01-01 00:00:00.0 SELECT CAST(@DateTime AS datetime2(2)); --2016-01-01 00:00:00.03 SELECT CAST(@DateTime AS datetime2(3)); --2016-01-01 00:00:00.003 SELECT CAST(@DateTime AS datetime2(4)); --2016-01-01 00:00:00.0033 SELECT CAST(@DateTime AS datetime2(5)); --2016-01-01 00:00:00.00333 SELECT CAST(@DateTime AS datetime2(6)); --2016-01-01 00:00:00.003333 SELECT CAST(@DateTime AS datetime2(7)); --2016-01-01 00:00:00.0033333 GO DECLARE @DateTime datetime = '2016-01-01T00:00:00.007'; SELECT CAST(@DateTime AS datetime2(0)); --2016-01-01 00:00:00 SELECT CAST(@DateTime AS datetime2(1)); --2016-01-01 00:00:00.0 SELECT CAST(@DateTime AS datetime2(2)); --2016-01-01 00:00:00.01 SELECT CAST(@DateTime AS datetime2(3)); --2016-01-01 00:00:00.007 SELECT CAST(@DateTime AS datetime2(4)); --2016-01-01 00:00:00.0067 SELECT CAST(@DateTime AS datetime2(5)); --2016-01-01 00:00:00.00667 SELECT CAST(@DateTime AS datetime2(6)); --2016-01-01 00:00:00.006667 SELECT CAST(@DateTime AS datetime2(7)); --2016-01-01 00:00:00.0066667 GO
This behavior change provides a more accurate converted value but may break applications that expect the converted value to be the same as the rounded datetime value as was the case before SQL Server 2016.
Be aware than the full raw datetime precision (instead of the rounded value) is also used when evaluating predicates involving a datetime type. The full precision of both arguments are used, resulting in the equality compare predicate to evaluate to false in both scripts below. The greater than predicate is true in the first script and the less than predicate is true in the second:
--This script prints GREATER THAN predicate is true DECLARE @DateTime datetime = '2016-01-01T00:00:00.003'; DECLARE @DateTime2 datetime2(7) = @DateTime; IF @DateTime = @DateTime2 PRINT 'EQUAL predicate is true' ELSE PRINT 'EQUAL predicate is not true'; IF @DateTime < @DateTime2 PRINT 'LESS THAN predicate is true' ELSE PRINT 'LESS THAN predicate is not true'; IF @DateTime > @DateTime2 PRINT 'GREATER THAN predicate is true' ELSE PRINT 'GREATER THAN predicate is not true'; GO --This script prints LESS THAN predicate is true DECLARE @DateTime datetime = '2016-01-01T00:00:00.007'; DECLARE @DateTime2 datetime2(7) = @DateTime; IF @DateTime = @DateTime2 PRINT 'EQUAL predicate is true' ELSE PRINT 'EQUAL predicate is not true'; IF @DateTime < @DateTime2 PRINT 'LESS THAN predicate is true' ELSE PRINT 'LESS THAN predicate is not true'; IF @DateTime > @DateTime2 PRINT 'GREATER THAN predicate is true' ELSE PRINT 'GREATER THAN predicate is not true'; GO
To provide insight into why the comparisons result in greater than and less than respectively, the script below shows the nanoseconds value of the compared data types:
DECLARE @DateTime datetime = '2016-01-01T00:00:00.003'; DECLARE @DateTime2 datetime2(7) = @DateTime; SELECT DATEPART(nanosecond, @DateTime); --3333333 SELECT DATEPART(nanosecond, @DateTime2); --3333300 GO DECLARE @DateTime datetime = '2016-01-01T00:00:00.007'; DECLARE @DateTime2 datetime2(7) = @DateTime; SELECT DATEPART(nanosecond, @DateTime); --6666666 SELECT DATEPART(nanosecond, @DateTime2); --6666700 GO
The datetime2 type is accurate only to 100 nanosecond whereas datetime includes values to the nanosecond (and beyond) because the theoretical precision of repeating decimal values is unlimited. The implication is that a datetime type with a repeating decimal value will never compare equally with any temporal type except datetime.
Datetime conversion and comparison behavior is controlled by the database compatibility level. Databases in SQL Server 2016 level (130) use the new behavior and the legacy behavior is used with other levels.
Summary
These datetime behavior changes have the benefit of improved accuracy and performance of datetime conversion/comparison. Affected applications can use a pre-SQL Server 2016 database compatibility level until they can be remediated.
I recommend one avoid comparing datetime directly to other temporal types going forward. Instead convert the datetime value to the type being compared and use the converted value in the predicate. It’s generally best practice to match data types whenever possible for performance and to avoid ambiguity.