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:
