Why does conversion from DATETIME to DATETIME2 appear to change value?

Question

I had a stored procedure comparing two dates. From the logic of my application, I expected them to be equal. However, the comparison failed. The reason for this was the fact that one of the values was stored as a DATETIME and had to be CONVERT-ed to a DATETIME2 before being compared to the other DATETIME2. Apparently, this changed its value. I have run this little test:

DECLARE @DateTime DATETIME='2018-01-18 16:12:25.113'
DECLARE @DateTime2 DATETIME2='2018-01-18 16:12:25.1130000'
SELECT @DateTime, @DateTime2, DATEDIFF(NANOSECOND, @DateTime, @DateTime2)

Which gave me the following result:

Why is there the difference of 333333ns between these values? I thought that a DATETIME2, as a more precise type, should be able to accurately represent all the values which can be stored in a DATETIME? The documentation of DATETIME2 only says:

When the conversion is from datetime, the date and time are copied. The fractional precision is extended to 7 digits.

No warnings about the conversion adding or subtracting 333333ns to or from the value! So why does this happen?

I am using SQL Server 2016.

edit: Strangely, on a different server I am getting a zero difference. Both are SQL Server 2016 but the one where I have the problem has compatibility level set to 130, the one where I don't has it set to 120. Switching between them changes this behaviour.

edit2: DavidG suggested in the comments that the value I am using can be represented as a DATETIME2 but not a DATETIME. So I have modified my test to make sure that the value I am assigning to @DateTime2 is a valid DATETIME value:

DECLARE @DateTime DATETIME='2018-01-18 16:12:25.113'
DECLARE @DateTime2 DATETIME2=CONVERT(DATETIME2, @DateTime)
SELECT @DateTime, @DateTime2, DATEDIFF(NANOSECOND, @DateTime, @DateTime2)

This helps a little because the difference is smaller but still not zero:

Answer 1

datetime2 is shorthand for datetime2(7), which indicates you want 7 digits for fractional seconds (the maximum). Try a datetime2(3) if you want something closer to a datetime.

Also, be aware that datetime2(3) is more precise than a datetime. The latter rounds to the nearest 0.000, 0.003, or 0.007 by design.

Answer 2

A breaking change was introduced in SQL Server 2016 with regards to conversion and comparison of datetime and datetime2. The changes are detailed in this knowledge base article.

In summary, values were rounded during the conversion in SQL 2014 and earlier versions whereas the full precision is considered nowadays. This improves performance but introduces issues when converting and comparing these unlike types.

Answer 3

Based on this MSDN blog post, DATETIME precision is .00333 seconds, while DATETIME2 (or DATETIME2(7) explicitly) has 100 ns precision. So even comparing DATETIME to DATETIME2(3), which would seem to have the same precision, DATETIME2(3) is more precise.

This weird 3.33 ms precision of DATETIME is the reason why when comparing seemingly equal values, you can get a difference.

Answer 4

To me, when you do comparison, you actually should convert data with high precision to low precision to avoid such "difference"

DECLARE @DateTime DATETIME='2018-01-18 16:12:25.113'
DECLARE @DateTime2 DATETIME2='2018-01-18 16:12:25.1130000'
SELECT @DateTime, cast(@DateTime2 as datetime), DATEDIFF(NANOSECOND, @DateTime, cast(@DateTime2 as datetime))

The result is