EF DateTimes do not match saved values in SQL Server

Question

I save a datetime using C# Entity Framework, and when I load that time back from the database, the time varies from the value that I saved by 1 or more milliseconds.

Here is the C# Code:

    public List<DateTime> TestDate()
    {
        var dates = new List<DateTime>();
        DateTime testvalue = DateTime.Now;
        dates.Add(testvalue);
        IactexGMG2Entities firstContext = new IactexGMG2Entities();
        var firstQuery = from p in firstContext.LocationProperties
                         where p.locationPropertyId == 4
                         select p;
        var firstRec = firstQuery.Single();
        firstRec.locationPropertyDateTime = testvalue;
        firstContext.SaveChanges();
        firstContext.Dispose();
         IactexGMG2Entities secondContext = new IactexGMG2Entities();
         var secondQuery = from p in secondContext.LocationProperties
                          where p.locationPropertyId == 4
                          select p;
        var secondRec = secondQuery.Single();

        var secondDate = secondRec.locationPropertyDateTime ?? DateTime.Now;
        dates.Add(secondDate);
        secondContext.Dispose();
        return dates;
    }

Here are the received values:

5/29/2015 5:43:25 PM . 154 , 635685182051540566
5/29/2015 5:43:25 PM . 153 , 635685182051530000

Here is the razor code that displays the values:

@foreach (var date in Model)
{
    counter++;
    <div>
        @date . @date.Millisecond , @date.Ticks 
    </div>
}

As you can see, the second value, which was read back from the database, is lower than the first value by 1.0566 milliseconds.

The amount of variation varies, positive and negative, always with a small number of milliseconds.

Does anyone know how the conversion between the date values takes place?

Note: If I use the same context to read the date value, the values match. I assume that is because it is using the cached value, rather than the SQL Server value.

What is the data-type of the column in SQL Server? SQL Server's `datetime` type does not offer the same resolution as `System.DateTime`. — Dai, May 29 '15 at 22:00
Where are the milisecond values coming from? If you're storing `DateTime.Now` (or `DateTime.UtcNow`) in the database consider rounding them to the nearest second before saving them. — Dai, May 29 '15 at 22:01
Have you looked at this post: http://stackoverflow.com/questions/7823966/milliseconds-in-my-datetime-changes-when-stored-in-sql-server — David Tansey, May 29 '15 at 22:02

score 6 · Accepted Answer · answered May 29 '15 at 22:07

The problem is the different resolution between TSQL datetime and .NET DateTime data types

datetime only has a small resolution and is rounded to increments of .000, .003, or .007 seconds, whereas DateTime has a resultion of 100ns

Just use the new datetime2 SQL Data Type, which has the same resolution as .NET's DateTime, which is anyway recommended in new work, exactly for the issue you noticed

score 2 · Answer 2 · answered May 29 '15 at 22:11

This actually has very little to nothing to do with Entity Framework. SQL Server as of 2008 has two DateTime types:

DateTime

Accuracy : Rounded to increments of .000, .003, or .007 seconds

DateTime2

Accuracy : 100 nanoseconds

Using Code-First Annotations you can set the property type like:

public MyClass
{
    [Column(“CreatedOn", TypeName="DateTime2")] 
    public DateTime CreatedOn { get; set; }
}

Or using Fluent API:

modelBuilder.Entity<MyClass>()
  .Property(p => p.CreatedOn)
  .HasColumnType("DateTime2");

EF DateTimes do not match saved values in SQL Server

2 Answers2