How can I account for datetime's lack of millisecond precision when performing datetime comparisons?

Question

Background

I recently learned that SQL Server's datetime type only stores time to approximately 1/300th of a second.

So given these data:

create table datetime_test (id int, my_date datetime);
insert into datetime_test (id, my_date) values
    (0, '2020-12-11 17:14:07.000'),
    (1, '2020-12-11 17:14:07.197'),
    (2, '2020-12-11 17:14:07.198'),
    (3, '2020-12-11 17:14:08.000');

This query would return (1, 2, 3), instead of (2, 3) as one might expect:

select id from datetime_test where my_date >= '2020-12-11 17:14:07.198';

The reason is that the millisecond part of this datetime is actually stored as .197:

-- Result: 2020-12-11 17:14:07.197
select convert(datetime, '2020-12-11 17:14:07.198');

My Question

I am working with existing c# code that uses SQL to compare datetimes using >=. Something like this:

public Foo GetMyColumn(DateTime inputDatetime)
{
  // ...
}

select my_column
from my_table
where my_datetime >= @inputDatetime

I am trying to reuse this c# method to perform an exclusive comparison...the equivalent of using >. How can I do that?

My original attempt was to add a single millisecond to the datetime input (in c#), but that will not work due to the precision issue outlined above. I suppose I could add 3 milliseconds. That feels like a hack. But would it work reliably?

Note: please assume I cannot change this SQL or method.

This is. a good reason to use `datetime2` which has much more control over precision (https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver15). — Gordon Linoff, Dec 14 '20 at 14:49
@GordonLinoff agreed. But in this case, I am working with an existing codebase/database that would be very painful to update. So I am wondering if there is a workaround. — srk, Dec 14 '20 at 14:54
. . I think your hack should add 4 milliseconds, because sometimes adding 3 just gets you to the same value. But, I suspect that you might be able to do some of the work in the database rather than in C# code, which might be the safest method. — Gordon Linoff, Dec 14 '20 at 15:06
Consider row 2 - the only way to retrieve it is to use the `>= 197` query or equivalent. The same transformation that's happening to your querying date is happening when storing dates. There's no way to modify just that query to retrieve just rows 2 and 3 based on a datetime based comparison. Or to put it another way `.197` and `.198` are *indistinguishable* in the database. — Damien_The_Unbeliever, Dec 14 '20 at 15:21
@Damien_The_Unbeliever - sorry, I can see how my background info would be confusing. I want to query the data as it exists in the database, not as I have written the `insert` statement. So in this case, the answer I would be looking for is simply `(3)`. — srk, Dec 14 '20 at 15:23
I see [here](http://www.sqlines.com/sql-server/datetime_or_datetime2_3) that `datetime` rounds milliseconds to `.xx0`, `.xx3`, or `.xx7`. So assuming that is true, I suppose I could round my input up to the next number, following that pattern. Of course this is a big hack, but just exploring my options. — srk, Dec 14 '20 at 15:38

Walter Verhoeven · Answer 1 · 2020-12-14T15:34:37.383

You will find that the windows clock may not be as accurate as you may need it to be. Also, your server's clock is not that accurate, depending on the load it may be lagging, for this reason, we use special hardware with GPS timer when an accurate time is needed.

Also, the time on your server and the time on the DB server does not need to match, if your requirements are hard then they will/ may "never match". Only a broken watch is accurate 2x per day...

Just a small note, always use UTC date-time on the database and in code, this way you have the same time regardless of where the servers are hosted. UTC date-time has a ToLocalTime() in .net and the database has GetUtcDate() in TSQL this way you are not off by time-zones.

using System;
using System.Runtime.InteropServices;
public static class HighResolutionDateTime 
{ 
    public static bool IsAvailable { get; private set; }
    [DllImport("Kernel32.dll", CallingConvention = CallingConvention.Winapi)] 
    private static extern void GetSystemTimePreciseAsFileTime(out long filetime); 
    public static DateTime UtcNow 
    {
        get { 
            if (!IsAvailable) 
            { 
                throw new InvalidOperationException("High resolution clock isn't available."); 
            } 
            long filetime; 
            GetSystemTimePreciseAsFileTime(out filetime); 
            return DateTime.FromFileTimeUtc(filetime); 
        } 
    } 
    static HighResolutionDateTime() 
    { 
        try 
        { 
            long filetime; 
            GetSystemTimePreciseAsFileTime(out filetime); 
            IsAvailable = true; 
        } 
        catch (EntryPointNotFoundException) 
        {             // Not running Windows 8 or higher.             
            IsAvailable = false;         
        }     
    } 
}

#edit as per comment as to comparing the date-time, use datetime2(7) as datatype in your data column, this will be as accurate as .net datetime

The original date input comes from a separate database query, so I don't think I need to worry about the clocks being out of sync. — srk, Dec 14 '20 at 15:09
Also forgive me, but is there an answer to my question here? I see a bunch of interesting side-notes, but nothing that directly addresses my question. — srk, Dec 14 '20 at 15:27
so save your data in a DateTime2(7) field in the database and you will round to SQL servers clock. — Walter Verhoeven, Dec 14 '20 at 15:27

score 0 · Accepted Answer · answered Dec 14 '20 at 15:53

0

According to the documentation, datetime is "rounded to increments of .000, .003, or .007 seconds." So the c# workaround is to round up the DateTime input to the next number, following that pattern.

Something like this:

private DateTime RoundUp(DateTime datetime)
{
    int lastMillisecondDigit;
    do
    {
        datetime = datetime.AddMilliseconds(1);
        lastMillisecondDigit = datetime.Millisecond % 10;
    } while (lastMillisecondDigit != 0 && lastMillisecondDigit != 3 && lastMillisecondDigit != 7);

    return datetime;
}

Note: I understand this is a big hack. There are better solutions in the comments, such as changing the datatype to datetime2. But if you are unable to change the database or code, I think this workaround will do the trick.

answered Dec 14 '20 at 15:53

srk

1,625
1
10
26

I gave him a precise time method, he did not like that – Walter Verhoeven Dec 14 '20 at 15:56
@WalterVehoeven I appreciate your input, but respectfully, you did not answer my question. My question was how to implement a workaround with an existing code/database. I agree that `datetime2` is a better option if you can change the database (as I noted in this answer), but that wasn't my question. – srk Dec 14 '20 at 16:09
Than you are going to have to measure the difference as being less than timespan difference DbTime-localtime> Timespan. you have the same when comparing doubles – Walter Verhoeven Dec 14 '20 at 17:58

How can I account for datetime's lack of millisecond precision when performing datetime comparisons?

Background

My Question

2 Answers2