How to compare dates with different precision

Question

I asked this question regarding how to compare datetime2 values, but ignoring the nanoseconds. Since there is some rounding involved, it fails to compare the values properly.

In java, I can get a Long-value from Date and Timestamp which represents the milliseconds since January 1, 1970, 00:00:00 GMT. This is a pretty safe way to compare dates in java.

Is there a way to either

A) Get a numeric value from a datetime2 value which on represents the amount of milliseconds

B) Alter the rounding from the milliseconds to round down instead of up.

Example values:

2018-01-24 16:20:51.0715460
2018-01-25 09:52:04.1946950
2018-01-25 09:52:04.1946950

should match with

2018-01-24 16:20:51.0710000
2018-01-25 09:52:04.1940000
2018-01-25 09:52:04.1940000

Currently I am using cast(date as datetime2(3)) to receive the most precise result, but they still don't match due to rounding.

Can anyone give me a convenient way to compare the values above to match?

GarethD · Answer 1 · 2018-01-29T14:10:47.457

You can do this by extracting the number of milliseconds from your unrounded date, use the modulus function to get the remainder from 1000000, then deduct this from your original date, e.g.

SELECT  A,
        B,
        RoundDown = DATEADD(NANOSECOND, -DATEPART(NANOSECOND, A) % 1000000, A),
        Match = CASE WHEN DATEADD(NANOSECOND, -DATEPART(NANOSECOND, A) % 1000000, A) = B THEN 1 ELSE 0 END
FROM    (VALUES
            (CONVERT(DATETIME2(7), '2018-01-24 16:20:51.0715460'), CONVERT(DATETIME2(7), '2018-01-24 16:20:51.0710000')),
            (CONVERT(DATETIME2(7), '2018-01-25 09:52:04.1946950'), CONVERT(DATETIME2(7), '2018-01-25 09:52:04.1940000')),
            (CONVERT(DATETIME2(7), '2018-01-25 09:52:04.1946950'), CONVERT(DATETIME2(7), '2018-01-25 09:52:04.1940000'))
        ) x (A, B);

Gives:

A                               B                               RoundDown                       Match
-------------------------------------------------------------------------------------------------------
2018-01-24 16:20:51.0715460     2018-01-24 16:20:51.0710000     2018-01-24 16:20:51.0710000     1
2018-01-25 09:52:04.1946950     2018-01-25 09:52:04.1940000     2018-01-25 09:52:04.1940000     1
2018-01-25 09:52:04.1946950     2018-01-25 09:52:04.1940000     2018-01-25 09:52:04.1940000     1

For comleteness, here is the same thing with normal rounding, rounding up, and rounding down

SELECT  Original = A,
        RoundDown = DATEADD(NANOSECOND, -DATEPART(NANOSECOND, A) % 1000000, A),
        RoundUp = DATEADD(NANOSECOND, 1000000 - DATEPART(NANOSECOND, A) % 1000000, A),
        RoundNormal = CONVERT(DATETIME2(3), A)
FROM    (VALUES
            (CONVERT(DATETIME2(7), '2018-01-24 16:20:51.0715460')),
            (CONVERT(DATETIME2(7), '2018-01-25 09:52:04.1946950')),
            (CONVERT(DATETIME2(7), '2018-01-25 09:52:04.1946950')),
            (CONVERT(DATETIME2(7), '2018-01-25 09:52:04.1943950'))
        ) x (A);

score 1 · Accepted Answer · answered Jan 29 '18 at 13:59

One quick option is to convert to varchar(23) which will essentially truncate the value

Example

Declare @YourTable Table ([D1] datetime2)
Insert Into @YourTable Values 
 ('2018-01-24 16:20:51.0715460')
,('2018-01-25 09:52:04.1946950')
,('2018-01-25 09:52:04.1946950')

Select * 
      ,NewVal = convert(varchar(23),D1)+'0000'
from @YourTable

Returns

D1                             NewVal
2018-01-24 16:20:51.0715460    2018-01-24 16:20:51.0710000
2018-01-25 09:52:04.1946950    2018-01-25 09:52:04.1940000
2018-01-25 09:52:04.1946950    2018-01-25 09:52:04.1940000

How to compare dates with different precision

2 Answers2