Weird datetime and dateime2 millisecond comparison issue

Question

I have been facing typical comparison issue with datetime and datime2. I am unable to understand why in 1 & 2 doesn't return equal result. I have read about how datetime value stored from msdn and based on that for 1 - it should have returned equal as 993 will be rounded off to 993 and comparing with 99300000 should have resulted equal. I am using SQL Server 2014.

Please let me know if anyone has idea about the same.

1)

declare @dtest datetime2(7)
set @dtest = '2018-06-25 16:46:38.9930000'

declare @dtest1 datetime
set @dtest1 = '2018-06-25 16:46:38.993'

if @dtest < @dtest1
    print 'datetime2 lesser'

2)

declare @dtest2 datetime2(7)
set @dtest2 = '2018-06-25 16:46:38.9970000'

declare @dtest3 datetime
set @dtest3 = '2018-06-25 16:46:38.997'

if @dtest2 > @dtest3
    print 'datetime2 greater'

3)

declare @dtest4 datetime2(7)
set @dtest4 = '2018-06-25 16:46:38.9900000'

declare @dtest5 datetime
set @dtest5 = '2018-06-25 16:46:38.990'

if @dtest4 = @dtest5
    print 'datetime2 and datetime equal'

1 and 2 do both return equal results for me – Martin Smith Jun 26 '18 at 18:51 — Martin Smith, Jun 26 '18 at 18:51

UnhandledExcepSean · Accepted Answer · 2018-06-26T19:00:54.490

2

The issue is that SQL Server is implicitly casting to make the comparison and that is changing the values. Explicitly cast to Datetime and you should get the results you are expecting.

This helps show what is happening behind the scenes now that is causing unexpected results:

declare @dt2 datetime2(7) = '2018-06-25 16:46:38.9930000'
declare @dt datetime
set @dt = @dt2

SELECT
        @dt2 AS [Datetime2 value]
    ,   @dt AS [Datetime value]
    ,   CONVERT(DATETIME2,@dt) AS [Datetime converted to Datetime2]
    ,   CONVERT(DATETIME2,@dt2) AS [Datetime2 converted to Datetime2]
    ,   CONVERT(DATETIME,@dt) AS [Datetime converted to Datetime]
    ,   CONVERT(DATETIME,@dt2) AS [Datetime2 converted to Datetime]

edited Jun 26 '18 at 19:00

answered Jun 26 '18 at 17:51

UnhandledExcepSean

12,504
2
35
51

So I've run that and have no idea what point you're making. It returns this https://i.stack.imgur.com/0oZAo.png. Are you saying the trailing zeroes are significant? – Martin Smith Jun 26 '18 at 18:50
@MartinSmith I'm saying his SQL is implicitly casting the DATETIME as DATETIME2, so 2018-06-25 16:46:38.993 becomes 2018-06-25 16:46:38.9933333. – UnhandledExcepSean Jun 26 '18 at 18:53
Hmmm, not what I see but I do vaguely remember there being some breaking change in this area – Martin Smith Jun 26 '18 at 18:55
@MartinSmith If it helps, my SQL Server version is 14.0.1000.169 – UnhandledExcepSean Jun 26 '18 at 18:56
I think I read about the change here https://stackoverflow.com/a/48327331/73226 but when I click through to the KB article linked there I don't see the case discussed. The screenshot I posted is from `12.0.2569.0`. The OP says they are on 2014 – Martin Smith Jun 26 '18 at 19:00
@UnhandledExcepSean & Martin Smith -Thank you for providing insight and details which helped to figure out the issue. Sorry my bad server is using SQL server 2016 and due to this it is failing. – Punit Jun 28 '18 at 19:19
@UnhandledExcepSean This also started happening because entity framework treats datetime to datetime2 and send all datetime field as datetime2 to SQL. – Punit Jun 28 '18 at 19:41
just happened to come across the relevant bit here https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017 `An example of a breaking change protected by compatibility level is an implicit conversion from datetime to datetime2 data types. Under database compatibility level 130, these show improved accuracy by accounting for the fractional milliseconds, resulting in different converted values. To restore previous conversion behavior, set the database compatibility level to 120 or lower.` – Martin Smith Aug 22 '18 at 21:50

score 0 · Answer 2 · edited Jun 26 '18 at 19:03

0

datetime and datetime2 have different internal storage formats and resolutions.

https://sqlfascination.com/2009/10/11/what-is-the-sql-server-2008-datetime2-internal-structure/

datetime2 uses .0000001 seconds per time unit (increment of the time counter), while datetime uses 0.00333 seconds

0.9970000 can't be represented as an integer multiple of 0.00333, so the value in your example will not match when compared between the two representations.

edited Jun 26 '18 at 19:03

Martin Smith

438,706
87
741
845

answered Jun 26 '18 at 17:47

Terry Carmen

3,720
1
16
32

Datetime doesn't use precisely 0.00333 seconds. It has three digit precision with the third digit being restricted to one of `0`, `3` or `7` – Martin Smith Jun 26 '18 at 18:49
But it looks as though at some point behaviour must have changed as to whether to conceptually treat it that way or not. – Martin Smith Jun 26 '18 at 19:04
@MartinSmith: Not sure what you mean here. datetime2 was introduced in SQL Server 2008. I don't think datetime or datetime2 has changed internally since then (although it's certainly possible) – Terry Carmen Jun 26 '18 at 19:31
How they are stored internally isnt relevant. What seems to have changed is whether a datetime previously presented as `0.003` for example is conceptually regarded as just `0.003` or as `0.0033333...` when it comes to converting them. This is an issue of what is the correct semantic behaviour and is orthogonal to how they are stored internally. – Martin Smith Jun 26 '18 at 19:38

Weird datetime and dateime2 millisecond comparison issue

2 Answers2