CODEWITHSUNDEEP
sqlsql-serverdatetimemillisecondsdatetime2
Punit
Punit

Reputation: 1445

Weird datetime and dateime2 millisecond comparison issue

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'

Upvotes: 1

Views: 1058

Answers (2)

Terry Carmen
Terry Carmen

Reputation: 3886

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.

Upvotes: 0

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

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]

enter image description here

Upvotes: 2

Related Questions