none
Change in datetime2 implementation in SQL Server 2016

    Question

  • We upgraded a database from SQL Server 2012 to 2016 and encountered problems with updating records using Entity Framework 6.  We traced the problem back to a difference between version 2016 and earlier versions in how datetime values are compared to datetime2 values. To illustrate, running the following query where the "datemodified" column is a datetime data type:

    select datemodified date1, cast(datemodified as datetime2(3)) date2, cast(datemodified as datetime2(7)) date3,
    case when datemodified <> cast(datemodified as datetime2(3))
    then 'unequal' else 'equal' end as datesareequal from applicationsettings

    provides the following results:

     date1                                     date2                                     date3                            datesareequal
    2016-02-17 14:46:39.343   2016-02-17 14:46:39.343   2016-02-17 14:46:39.3433333   unequal
    2016-03-22 08:47:38.000   2016-03-22 08:47:38.000   2016-03-22 08:47:38.0000000   equal
    2016-08-16 13:55:25.593   2016-08-16 13:55:25.593   2016-08-16 13:55:25.5933333   unequal
    2016-05-11 11:16:24.000   2016-05-11 11:16:24.000   2016-05-11 11:16:24.1400000   equal
    2016-02-15 15:52:15.187   2016-02-15 15:52:15.187   2016-02-15 15:52:15.1866667   unequal
    2016-03-18 16:01:43.000   2016-03-18 16:01:43.000   2016-03-18 16:01:43.0000000   equal

    In SQL 2012 & 2014, the query above returns "equal" for all results because date1 is considered equal to date2, but in SQL 2016 the results above are returned because date1 and date2 are considered as being unequal in cases where the (converted) date3 value has a precision beyond 3 digits (e.g., 2016-02-17 14:46:39.3433333 versus 2016-02-17 14:46:39.3434000).

    Is this expected behavior that is new in SQL 2016 and, if so, how do we correctly coerce datetime and datetime2 values when such comparisons are needed?

    Wednesday, August 17, 2016 9:56 PM

Answers

All replies

  • This problem was reported to Microsoft Connect.  You can find it at https://connect.microsoft.com/SQLServer/Feedback/Details/2805477

    You can go there, sign in and vote it up.  You can also click on the I can reproduce this.  And consider adding a comment explaining why this causes a business problem for you and emphasizing that this change broke currently working code.  All of those actions will make it more likely that Microsoft prioritizes this problem.

    Tom

    Thursday, August 18, 2016 2:42 AM
  • This is a deliberate change in SQL 2016, which appears only if you have the compatibility set to 130. When converted to datetime2, datetime values now get the full suite of fractions. I'm a little puzzled of the results from the CASE expression, because the datetime value should be cast to datetime2(3) and the extra decimals should not matter.

    Thursday, August 18, 2016 7:22 AM
  • Sorry for the slow reply back, and thanks for the answer and link to MS connect.  I've upvoted and added a comment with more detail, including reference to the datetime <> cast(datetime as datetime2(3)) issue noted by Erland.

    The change in value by increasing precision of an existing number is really unintuitive.  If pi (3.14159265359...) is rounded to 3.141 and then cast to decimal(7,6) you get 3.141000, not 3.141593.  I doesn't seem appropriate for dates to behave differently.

    The short answer for us was to set the database compatibility to SQL 2014. 

    Thursday, September 1, 2016 10:39 PM
  • The short answer for us was to set the database compatibility to SQL 2014. 

    I hope that you are aware of that this is a short-term solution. Compat level 120 will not be supported for ever, so you will have to adapt your code at some point.

    I have spoken with some Microsoft people about this, and what is really happening is that datetime values are reinterpreted to something which is more accurate. Saying that the fraction is .003 is inaccurate as it really is 1/300 of a second. To take your pi example, the old behaviour is as if the pi() function would return 3.142.

    I will have to admit that I not entirely convinced that this difference is worth a breaking change. On the other hand, I am not sure that I see the wise in mixing datetime and datetime2 in an application.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, September 2, 2016 10:28 PM
  • I hope that you are aware of that this is a short-term solution. Compat level 120 will not be supported for ever, so you will have to adapt your code at some point.

    ...what is really happening is that datetime values are reinterpreted to something which is more accurate.

    I am not sure that I see the wise in mixing datetime and datetime2 in an application.

    Thanks for the warning - I'm definitely aware the compatibility-level work-around is not a long term fix.

    The fact that "datetime values are reinterpreted" is what is most concerning here.  Storage of truncated values is very common, and "reinterpreting them" because of a cast to greater precision will definitely result in unexpected behavior as it did for us.  In any case, Microsoft's reasoning doesn't explain why the datetime DateModified value in my original example is unequal to cast(DateModified as datetime2(3)).

    You may be right in avoiding mixing of datetime and datetime2, but many of our legacy applications have a lot of datetime columns, not to mention APIs that exchange datetime values with other systems.  It's unclear what risk we would be taking on to convert them to datetime2 given that years of existing data will be "reinterpreted" if we do. 

    The root of our problem at the moment is that Entity Framework (v6.1.3) is choosing to cast datetime parameter values to datetime2 and then compare them to datetime values in the database (for concurrency check).  The "reinterpreted" datetime values don't match the original datetime value, so the concurrency test fails when it shouldn't.  This is of course a topic for a different thread.

    Thursday, September 8, 2016 1:24 AM
  • Interesting about EF. Can you please elaborate in more details?

    In our application we're using datetime columns. 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, September 8, 2016 9:25 AM
    Moderator
  • The fact that "datetime values are reinterpreted" is what is most concerning here.  Storage of truncated values is very common, and "reinterpreting them" because of a cast to greater precision will definitely result in unexpected behavior as it did for us.  In any case, Microsoft's reasoning doesn't explain why the datetime DateModified value in my original example is unequal to cast(DateModified as datetime2(3)).

    Because there is really no such thing as a datetime value of 2016-02-17 14:46:39.343, it is 2016-02-17 14:46:39.3433333...
    The new thing is that in SQL 2016 it is handled as such.

    The root of our problem at the moment is that Entity Framework (v6.1.3) is choosing to cast datetime parameter values to datetime2 and then compare them to datetime values in the database (for concurrency check).  The "reinterpreted" datetime values don't match the original datetime value, so the concurrency test fails when it shouldn't.  This is of course a topic for a different thread.

    I agree that if a change in SQL 2016 pulls the rug for another Microsoft product that is bad. I was not aware of that this was the situation, as EF is not something I use myself.

    Thursday, September 8, 2016 9:59 PM
  • Did you try setting the providermanifesttoken to 2005 in your EDMX file?

    http://stackoverflow.com/questions/316422/using-sql-server-2008-and-sql-server-2005-and-date-time


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Friday, September 9, 2016 5:47 AM
  • I was researching another problem (decimal precision) and came upon this link

    https://github.com/richardlawley/EntityFrameworkAttributeConfig from this StackOverflow 

    http://stackoverflow.com/questions/9032919/set-decimal16-3-for-a-column-in-code-first-approach-in-ef4-3

    So, seems there is a way to specify precision (0) for datetime in EF and precision for decimal either using fluent API or through these attributes (wish it would be part of EF, but it doesn't look like).


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, October 6, 2016 1:12 PM
    Moderator
  • Naomi - we ran into the problem with EF (v6) because of a datetime column being used for the concurrency check.  If you use timestamp columns for concurrency you are probably okay. 

    Otherwise, when EF updates a record that has a concurrency column, the SQL update statement checks not only the PK column but tests the concurrency datetime value.

    Example of existing record, where Timestamp column is datetime SQL data type:

    Record ID: fbec5a51-9ee7-4a0f-b0e3-06bb8f640c32, Timestamp: 2016-08-16 13:55:25.593, Color: "Blue"  

    EF will generate T-SQL similar to the following:

    update SomeTable set Color = "Red" where where ID = @1 and Timestamp = @2

    The parameters will look like:

    @1 = cast('fbec5a51-9ee7-4a0f-b0e3-06bb8f640c32' as uniqueidentifier)

    @2 = cast('2016-08-16 13:55:25.593' as datetime2)  -- YES, as datetime2 to compare to a datetime column

    The cast of parameter @2 value 2016-08-16 13:55:25.593 (which is the correct/current timestamp value)  to datetime2 in SQL 2016 gives a value of 2016-08-16 13:55:25.5933333. Intuitively its cast value should be 13:55:25.5930000.  Because 2016-08-16 13:55:25.5933333 does not equal 2016-08-16 13:55:25.593, the record is not found and EF thinks it doesn't exist (throws a concurrency exception if I recall correctly).

    In short, the only issue we ran into was for datetime columns used for concurrency check caused by EF's choice of datetime2 for casting a value compared against a datetime column.  Other operations (select/insert/update) with datetime/datetime2 columns seemed to work fine based on limited testing.   

    Wednesday, November 2, 2016 10:24 PM
  • Thanks for sharing.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, November 3, 2016 9:38 AM
    Moderator
  • Because there is really no such thing as a datetime value of 2016-02-17 14:46:39.343, it is 2016-02-17 14:46:39.3433333...
    The new thing is that in SQL 2016 it is handled as such.

    My understanding from MSDN is that the rounded value is what is stored (below is copied from that article):

    Rounding of datetime Fractional Second Precision

    datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

    User-specified value System stored value
    01/01/98 23:59:59.999 1998-01-02 00:00:00.000
    01/01/98 23:59:59.995

    01/01/98 23:59:59.996

    01/01/98 23:59:59.997

    01/01/98 23:59:59.998
    1998-01-01 23:59:59.997
    01/01/98 23:59:59.992

    01/01/98 23:59:59.993

    01/01/98 23:59:59.994
    1998-01-01 23:59:59.993
    01/01/98 23:59:59.990

    01/01/98 23:59:59.991
    1998-01-01 23:59:59.990

    If that's the case then while the "implicit" value of 2016-02-17 14:46:39.343 may be 14:46:39.3433333, the information above suggests what is actually stored is the value after rounding to 0.003 (or 0.000 or 0.007) has occurred.  So it seems that in SQL 2016 casting to datetime2 is taking an actual/stored value and converting it to an "implicit" value. Or is it that the stored value for datetime is equal to 14:46:39.3433333... but the displayed value is always rounded to 14:46:39.343, and that casting to datetime2 is basically allowing the additional precision to be revealed?

    Thursday, November 3, 2016 11:50 AM
  • Did you try setting the providermanifesttoken to 2005 in your EDMX file?

    http://stackoverflow.com/questions/316422/using-sql-server-2008-and-sql-server-2005-and-date-time


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com


    No, but good suggestion.  That would probably work since EF's date/time parameters would be cast to datetime and not datetime2, which was the source of our problem, and the app in question only has datetime columns.  I'm not sure if there would be undesirable consequences from rolling back providermanifesttoken to 2005 and so would prefer to leave it as-is.
    Thursday, November 3, 2016 12:23 PM
  • I have the same problem with Entity Framework 6 (code first).

    So what is the solution or workaround (except using COMPATIBILITY_LEVEL = 120)? Update all the Datetime columns to Datetime2 i na code first mgiration ?

    Wednesday, April 5, 2017 1:55 PM
  • With SQL Server 2016 SP1, it seems this issue has been resolved.

    My version: 

    Microsoft SQL Server 2016 (SP1-GDR) (KB3207512) - 13.0.4199.0 (X64)   Nov 18 2016 15:56:54   

    ---Edit:

    The problem still exists. My test database compatibility level was not was not up to date. It should be at 130 level.

    Wednesday, April 5, 2017 2:18 PM
    Moderator
  • Do you mean that EF datetime parameters are not converted to datetime2 or that the original code in this thread has been fixed and displays equal for everything?

    I think I found a way to set parameter to be treated as datetime (and not datetime2) using Fluent API, but I already forgot how.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, April 6, 2017 8:36 AM
    Moderator
  • ...  the original code in this thread has been fixed and displays equal for everything?

     

    Yes, the sample for this issue returns equal for every row now.
    Thursday, April 6, 2017 1:26 PM
    Moderator
  • I don't think so. I ran this on my machine ( SQL 2016 SP1 CU2):

    SELECT cast(getdate() as datetime2(7))

    The returned value was

    2017-04-07 00:03:24.5366667


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, April 6, 2017 10:09 PM
  • ...  the original code in this thread has been fixed and displays equal for everything?

     

    Yes, the sample for this issue returns equal for every row now.

    Doesn't seem to for me

    SELECT @@VERSION
    
    DECLARE @d DATETIME = '2016-02-17 14:46:39.343'
    
    SELECT @d AS date1, CAST(@d AS DATETIME2(3)) AS date2, CAST(@d AS DATETIME2(7)) AS date3, CASE WHEN @d = CAST(@d AS DATETIME2(3)) THEN 'Y' ELSE 'N' END AS equal
    -------------------------------------------------------------------------------------------------------------------------------------------
    Microsoft SQL Server 2016 (SP1-CU2) (KB4013106) - 13.0.4422.0 (X64) 
    Mar  6 2017 14:18:16 
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)


    (1 row(s) affected)

    date1                   date2                       date3                       equal
    ----------------------- --------------------------- --------------------------- -----
    2016-02-17 14:46:39.343 2016-02-17 14:46:39.343     2016-02-17 14:46:39.3433333 N

    (1 row(s) affected)


    • Edited by disssss Friday, April 7, 2017 2:28 AM
    Friday, April 7, 2017 2:27 AM
  • You guys are right.

    I forgot to change the database compatibility level to 130 (SQL Server 2016) after i migrate the user database from SQL Server 2014.

    The testing environment was not correct. It was in SQL Server 2016 with compatibility level 120 (SQL Server 2014).  

    The issue is still there.

    Friday, April 7, 2017 1:26 PM
    Moderator
  • Issue is same on 2016 SP1 CU5...

    My simple solution was to CAST(CURRENT_TIMESTAMP AS DATETIME) in our comparisons to the datetime2 datatype column.

    I would argue that this is our fault - you should never rely on implicit datatype rounding to be accurate. Just because it worked in earlier versions doesn't excuse bad coding practices.  


    Gary Melhaff, DW Architect, MCTS SqlServer 2008 BI, Developer

    Wednesday, November 22, 2017 12:17 AM
  • The issue is truly the combination of changes in SQL Server 2016 and in changes in Entity Framework v.6.  EFv6 began interpreting .Net DateTime as SQL Server datetime2(7) instead of previous datetime.

    SQL Server 2016 has changed the way it compares datetime and datetime2.  It is a documented "breaking change" 

    Please run the following query to see the result:

    if convert(datetime, '2018-02-14 10:11:47.473') = convert(datetime2(7), '2018-02-14 10:11:47.473')	print 'Equal'else	print 'Not Equal'

    For me it returns "Not Equal"

    Moreover, even if I change datetime2(7) to datetime2(3), which has the same precision as datetime, I get "Not Equal".

    Tuesday, March 20, 2018 5:51 PM
  • I found that the behavior is not consistent. Please run the following code in SQL Server 2016 to see how it depends on the value:

    DECLARE @dt datetime = '2018-06-26 14:50:32.253', @dt2 datetime2(3) = '2018-06-26 14:50:32.253'
    
    IF (@dt = @dt2)
    	PRINT 'Equal';
    ELSE
    	PRINT 'Not Equal';
    You will see: 'Not Equal'. Now change the last digit from 3 to 0 in both dates and run the same code:
    DECLARE @dt datetime = '2018-06-26 14:50:32.250', @dt2 datetime2(3) = '2018-06-26 14:50:32.250'
    
    IF (@dt = @dt2)
    	PRINT 'Equal';
    ELSE
    	PRINT 'Not Equal';

    This time you will see 'Equal'.

    PS: That behavior was reproduced in Microsoft SQL Server 2016 (SP1), Developer Edition (64-bit) - 13.0.4001.0

    PS2: It seems that it is a feature not a bug: Breaking Changes to Database Engine Features in SQL Server 2016

    Interesting bug with milliseconds and datetime on SQL Server 2016


    Alexey




    • Edited by AlexeyGa Monday, July 2, 2018 8:31 AM
    Thursday, June 28, 2018 7:15 AM
  • The implicit conversion between DATETIME2(3) and DATETIME doesn't work.

    The explicit conversion works on both directions : 

    1) DATETIME -> DATETIME2(3)

    2) DATETIME2(3) -> DATETIME

    DECLARE @dt datetime = '2018-06-26 14:50:32.253', @dt2 datetime2(3) = '2018-06-26 14:50:32.253'
    
    IF (CONVERT(DATETIME2(3),@dt)  = @dt2  
         AND  @dt  = CONVERT(DATETIME, @dt2))
    	PRINT 'Equal';
    ELSE
    	PRINT 'Not Equal';


    Wednesday, May 22, 2019 12:33 PM