CODEWITHSUNDEEP
c#datetime
Ash
Ash

Reputation: 389

Comparing equal datetimes returns false

I have a query with how datetimes are compared/stored in C#. Consider the following code:

var createdDate = DateTime.Now;
using (cr = new LanguageDictionaryRepository(ds)) {
    cr.Add(new Sybrin10.Data.DTO.LanguageDictionary() {
        Active = true,
        CreatedDate = createdDate,
        CultureCode = cultureCode,
        Data = new System.Text.UTF8Encoding().GetBytes("Test")
    });
    cr.Save();

    var y = cr.FindBy(x => x.CultureCode == cultureCode && x.CreatedDate == createdDate).FirstOrDefault();
    Assert.IsNotNull(y);

The Assert.IsNotNull is failing because of the datetime check. I would expect that as the LanguageDictionary instance is created with the variable's value that the two would be equal. This is using Telerik.OpenAccess and MSSQL as a DB layer so I'm assuming the problem comes in there. Can anyone tell me if there is anything I'm missing with this and how to correctly compare these values.

EDIT: The tick values are different but I don't know why as they both come from the same variable which I only assign to once.

Upvotes: 13

Views: 17531

Answers (3)

Hans Kesting
Hans Kesting

Reputation: 39283

SqlServer stores a datetime in (about) 3-millisecond increments.

datetime values are rounded to increments of .000, .003, or .007 seconds

A roundtrip of a DateTime through the database could thus be off by a few ms.

So you should not test for "exactly equal", but for "close enough"

var y = cr.FindBy(x => x.CultureCode == cultureCode && 
                        x.CreatedDate >= createdDate.AddMilliseconds(-5) && 
                        x.CreatedDate <= createdDate.AddMilliseconds(5))
         .FirstOrDefault();

Late edit: an extension method

public static class Extensions
{
    public static bool IsAboutEqualTo(this DateTime target, DateTime other)
        => Math.Abs((target - other).TotalMilliseconds) <= 4;
}

Usage

var y = cr.FindBy(x => x.CultureCode == cultureCode && 
           x.CreatedDate.IsAboutEqualTo(createdDate))
        .FirstOrDefault();

Do note that Entity Framework will not be able to translate this into SQL, it will only work in linq-to-objects.

Upvotes: 4

w5l
w5l

Reputation: 5746

Try using DateTime.Equals(x.CreatedDate, createdDate), it might help.

Other than that, proper DateTime comparing is a massively complicated subject with timezones, offsets, utc, local time and whatnot. I wouldn't at all be suprised at a simple == compare between two seemingly identical dates to return false.

If the Ticks value differs on write and read, you're might be facing a DateTimeKind problem, where you're writing a DateTimeKind.Local to the database, but getting back an DateTimeKind.Unspecified.

The other option could be (if the difference is small enough) that the DateTime field in your database is not significant enough to store the same amount of milliseconds as the .net DateTime:

A single tick represents one hundred nanoseconds or one ten-millionth of a second. There are 10,000 ticks in a millisecond.

Depending on your data storage, it might not be as detailed as this. Your DateTime values do not come from the same source, one is read from memory, the other is read from database.

Upvotes: 22

Michael Ciba
Michael Ciba

Reputation: 561

I think it might be better for you to use DateTime.UtcNow when you store the data and then you don't have to worry about daylight saving time issues etc. You can then display it how you want later using the culture you pick.

Upvotes: 0

Related Questions