Earlier last week, I was presenting various questions about SQL Server 2017 at PASS in Seattle. However, during one of the session, I was fortunate to meet SQL Server Expert Christopher Savage. He shared a very interesting puzzle with me. I have been trying to solve the puzzle for a while, but I have no answer for it. Let us see the puzzle about DateTime2 Conversion in SQL Server 2017.
Let us see a quick demonstration. Please note that this puzzle will work on only SQL Server 2017 compatibility level. If you are running your database on any earlier compatibility level, it will not work.
First, let us create a database and set up the compatibility level to SQL Server 2017.
CREATE DATABASE TestDB GO ALTER DATABASE [TestDB] SET COMPATIBILITY_LEVEL = 140 -- SQL Server 2017 GO
Next, run the following code. As I have specified GO 10 at the end of the query, it will run the query 10 times.
DECLARE @now DATETIME = GETDATE(); SELECT CASE WHEN CAST(@now AS DATETIME2) = @now THEN 1 ELSE 0 END, CAST(@now AS DATETIME2) as DT2, @now as DT GO 10
When you run the query, you will see the image which is similar to the following image.
You can see in the result we have results column changing the value between 1 and 0. Here is the puzzle for you.
Puzzle: Why does SQL Server change a value from 0 and 1 while we are always comparing the datetime in different data types.
Reference: Pinal Dave (https://blog.sqlauthority.com)
12 Comments. Leave new
Datetime2 has more precision value. Getting 1 where both are equal otherwise getting 0, so what’s the twist here?
Datetime2 has fraction in nano seconds And Datetime shows fraction in miliseconds, so when nanoseconds fully converted into milisecond (mans without decimal) these two values i.e. DateTime & DateTime2 are same result shows 1 else 0.
Actually datetime2 has a precision of 100ns, 7 fractions of a second, 1/10.000.000 of a second. datetime has a precision of appr 3ms, 3 fractions of a second, 1/300 of a second. In the case there is an implicit conversion of the datetime2 to datetime, and in the rounding occurring sometimes it ends up in the same 3ms timeframe.
Yep. exactly what the previous commenters wrote. Everywhere it is zero there is extra precision on the end of the value. Since you are comparing a DATETIME to a DATETIME2 the precision is what is messing you up.
I just tested it out on 2012, and 2016. The 2012 server shows ALL 1s. However, the 2016 server showed 0s and 1s like your query above so it looks like it is not limited to 2017 only.
Now as to why it ONLY happens on newer versions, that is another matter, and very confusing. AFAIK casting from a DATETIME to a DATETIME2 should not be adding extra precision…
Good one Pinal.
You may want to adjust your article title to Datetime to Datetime2. I know we all live in Datatime, but I figured you meant Datetime on the title. ?
@now is the default DATETIME type and DATETIME2 has larger datetime range and more precise value. So I think result is not much surprising!
DateTime is internally a float(53), where the whole numbers denote days and the decimals denote parts of days.
DateTime has a variable precision, the default is 7. If you change the precision to 3 to match datetime then the results are equal.
If you leave datetime2 as the default 7 then when the internal float is converted it’s done based on the float value.
This can be demonstrated by calculating the date ourselves from the float value:
“`
DECLARE @DateTime DATETIME = GETDATE();
DECLARE @Float FLOAT(53) = CAST(@DateTime AS FLOAT(53));
DECLARE @HH INT, @MM INT, @SS INT;
SET @Float = (@Float – cast(@Float AS INT)) * 24;
SET @HH = @Float;
SET @Float = (@Float – @HH) * 60;
SET @MM = @Float;
SET @Float = (@Float – @MM) * 60;
SET @SS = @Float;
SET @Float = (@Float – @SS);
SELECT [DateTime] = @DateTime
,[DateTime2(3)] = CAST(@DateTime AS DATETIME2(3))
,[DateTime2(7)] = CAST(@DateTime AS DATETIME2)
,[DateTimeManual] = CONCAT (
CAST(@DateTime AS DATE)
,’ ‘
,RIGHT(‘0′ + @HH, 2)
,’:’
,RIGHT(‘0′ + @MM, 2)
,’:’
,RIGHT(‘0′ + @SS, 2)
,’.’
,REPLACE(CAST(@Float AS DECIMAL(8, 7)), ‘0.’, ”)
)
;
“`
The built-in conversion has some “magic rounding” (always end with 0/3/7) which the manual version doesn’t have.
So I would say that in the later compatibility modes datetime to datetime2 conversion is being done on the internal value rather then the display value which is effected by precision settings.
https://docs.microsoft.com/en-us/sql/database-engine/breaking-changes-to-database-engine-features-in-sql-server-2016?view=sql-server-2017#SQL15
“Breaking Changes in SQL Server 2016 (13.x)
…
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, refer to this Microsoft Support Article.”
TL;DR: Use Explicit casts when comparing different datatypes as the Implicit cast might not do what you want.
AeroX is onto it I think. It’s a rounding error. It feels to me that DATETIME2 is the underlying type for DATETIME, which is why:
“The built-in conversion has some “magic rounding” (always end with 0/3/7) which the manual version doesn’t have.”
I think that the “magic rounding” doesn’t really exist.
I mean, if I developed a DATETIME routine, and wanted a more precise DATETIME2, I’d change the original routine and maintain backward compatibility using precision ie DATETIME is really DATETIME2(3).
So, if @now is DATETIME2(3) the CAST to DATETIME2(7) makes sense. When @now is assigned using GETDATE(), it is assigned with the seconds rounded to 3 decimal places, but manipulated and stored using 7 decimal places. It has to be to maintain compatibility. That means @now is a rounded figure stored as DATETIME(7), but displayed as DATETIME(3), and that’s where the “error” crops up: the CAST isn’t doing anything really – it’s just copying @now (with it’s rounding error) to a new DATETIME2.
That seems to fit for me :-)
You said what I was thinking, now I am not alone.
Hmm, yeah, the only chance for this to happen is, that datetime is internally stored with a higher precision than it is displayed. When casted, the full precision is used, when compared, not. My guess: It’s not a bug, it’s a feature.
Declare @D DateTime = ‘2019-10-28 11:38:33.887’
Select Cast(@D as DATETIME2)
Select Cast(‘2019-10-28 11:38:33.887’ as DATETIME2)
— Result
—————————
2019-10-28 11:38:33.8866667
(1 row affected)
—————————
2019-10-28 11:38:33.8870000
(1 row affected)