August 10, 2018 at 2:09 pm
Can someone please explain to me why executing following code in SQL Compatibility Level of 2016(level 130) and higher gives me an answer of 1.
When switched to Level of 2014(level 140) or lower gives me value of 0.
Statement:
select DATEDIFF(millisecond, convert(datetime, '2018-06-06 15:22:19.087', 121),convert(datetime2, '2018-06-06 15:22:19.087', 121)) [difference]
Issue occurs when EF generates and executes query against table column of datetime, yet expressing where clause with datetime2.
Appreciate your thoughts!
Best regards, Albert
August 10, 2018 at 3:01 pm
This is a documented change in behavior.
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.
In that linked article they give a bunch of examples where the result changes in compatibility level 130.
In this particular case, your datetime is getting converted implicitly to a datetime2, and under compatibility level 130 this conversion from datetime to datetime2 is "improved" in a way that gives you this surprising result.
The trick is that datetime values are stored as ticks that represent 1/300th of a second (mentioned in that support article, among other places).
What you see as .087 in the datetime format is 26 ticks, which is actually .086666....
Prior to 2016, when you converted to datetime2, which could better represent that, it kept .087. Starting in 2016, they let it be represented more accurately by the higher precision data type, but this does lead to the unexpected sort of behavior you describe, because once it's converted to datetime2, your start is 2018-06-06 15:22:19.0866667 and your end is 2018-06-06 15:22:19.0870000, and there is a millisecond boundary crossed.
Cheers!
August 11, 2018 at 2:28 am
Quick thought, for DATETIME2, the default fractional seconds precision is 7, if set to the same as the DATETIME, which is 3, then the result will be 0.
😎
SELECT DATEDIFF(MILLISECOND, CONVERT(DATETIME2(3),CONVERT(DATETIME, '2018-06-06 15:22:19.087', 121),0),CONVERT(DATETIME2(3), '2018-06-06 15:22:19.087', 121)) [DIFFERENCE]
It is very unlikely that anyone really needs this high fractional seconds precision and since it consumes more storage and in fact slows down operations on the DATETIME2 data type, I would advice against using it unless it is absolutely necessary.
August 11, 2018 at 12:50 pm
Jacob/Eirikur,
Thank you for your response. I appreciate explained behavior.
Issue stands from C# world reaching out through EntityFramework(EF) with IQueryable. Code generated by EF for WHERE clauseconverts all C# DateTime variables to datetime2, therefore causing above issue. Will be interesting to see developers take. All table columns will need to be updated to datetime2.
Brilliant!!!
Cheers, Albert
August 11, 2018 at 3:57 pm
roadfork - Saturday, August 11, 2018 12:50 PMAll table columns will need to be updated to datetime2.
Why? What do you have that needs the resolution of even 1/300th of a second offered by DATETIME never mind the 100 nano seconds of TIME and DATETIME(2)? And what do you have that is so memory critical that a two byte difference in size will make you or break you? Why are you willing to risk breaking your code by making a datatype change? Do you have a compelling reason to use any data prior to 01 Jan 1753 and, if you do, do you realize that the dates prior to that date are inaccurate because the older calendars weren't as accurate as the Gregorian calendar?
Are you also aware that DATE, TIME, and DATETIME2 are actually crippled when it comes to temporal calculations compared to DATETIME and even SMALLDATETIME making the supposed "best practice" of using DATETIME2 instead of using DATETIME one of the worst recommendations there is out there?
My recommendation would be to take a cold, hard, and thoughtful look at what the true cost of such a conversion may actually be in code breaks and regression testing. You've already got proof that it's not a one-to-one "safe" change if milliseconds matter.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 12, 2018 at 12:07 am
EntityFramework(EF) can use DATETIME, done so on many project, may take a "little" convincing of the developers though. EF unfortunately uses DATETIME2(7) by default, which is simply a waste of resources and with the poor implementation of the data type in SQL Server, it is also very slow!.
😎
DATETIME2 is on the paper a superior data type compared to DATETIME, but that is only on the paper. We have demonstrated many times on this site the difference between the two, especially when doing any kind of date / time manipulation and the difference is huge. My advice is not to use DATETIME2 unless absolutely necessary until MS fixes the SQL Server implementation of the data type (not holding my breath :pinch: )
August 13, 2018 at 11:06 am
Eirikur Eiriksson - Sunday, August 12, 2018 12:07 AMEntityFramework(EF) can use DATETIME, done so on many project, may take a "little" convincing of the developers though.
Thank you everyone for comments.
Found more information in following links:
https://github.com/aspnet/EntityFramework6/issues/578
https://github.com/aspnet/EntityFramework6/issues/49
Eirikur,
Can you please be more specific how you can achieve this without overwriting SqlProviderServices class? If easier to communicate offline please let me know.
Thank you, Albert
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply