SQL Server & EF7: Using datetime vs datetime2 Data Type

Abstract: SqlServer data type “datetime” has accuracy and rounding problems and has been superseded with the “datatime2” data type that has no such problems. But, “datetime” is still present in many legacy databases. We show how those problems in the .NET Entity Framework environment can lead to confusing situations.

Introduction

It all started as a practical problem. I was working on some legacy SqlServer database, writing my .NET/C# code, when I noticed WEIRED behavior when working with Timestamps. That pointed me to explore issues more in-depth, and here is this article. Originally, I saw issues in .NET 4.8 Framework/EF6 environment, but examples in this article are .NET7/EF7 Core environment and issues are still here.

The main problem is the limited accuracy of SqlServer data type “datetime” and rounding that is happening on the database side. A newer version of SqlServer data type “datetime2” has better accuracy and no rounding problem.

Usage of .NET Entity Framework just contributes to confusion during work with SqlServer data type “datetime”, since rounding of data happens when background SQL queries are executed in real but not when LINQ is executed in memory. Also, EF will try to fill your queries from EF Cache, so it might appear that you have better accuracy than you really have since data in EF Cache has better accuracy compared to real data in the database.

SqlServer data type “datetime” vs “datatime2”

Based on [1] and [2], here is a small table that outlines the differences between the SqlServer data types “datetime” and “datatime2” relevant to this article.

Sample database

We will try to show problems with some sample C# codes. For that, we need a small database that contains data types “datetime” and “datetime2”. Here is our small database, with the database table “People” which contains both data types.

C# Example 1

To demo problems, we created C# .NET7 test application and used Entity Framework 7, a Database-first approach (see [3]) to access the database.

Here is our program.

Here is the code of our Example 1.

using ExampleE1;
using ExampleE1.TestE1DB;

Console.WriteLine("Hello from ExampleE1");

//creating timestamps===============
Console.WriteLine("\nCreating timestamps===============");
// 2023-05-07-11:12:13.1234567
DateTime dt1 = new DateTime(2023, 5, 7, 11, 12, 13, 123, 456);
dt1 = dt1.AddTicks(7);
Console.WriteLine("dt1: " + dt1.ToString("yyyy-MM-dd-HH:mm:ss.fffffff"));

// 2023-05-07-11:12:13.1244567
DateTime dt2 = new DateTime(2023, 5, 7, 11, 12, 13, 124, 456);
dt2 = dt2.AddTicks(7);
Console.WriteLine("dt2: " + dt2.ToString("yyyy-MM-dd-HH:mm:ss.fffffff"));

// 2023-05-07-11:12:13.1224567
DateTime dt3 = new DateTime(2023, 5, 7, 11, 12, 13, 122, 456);
dt3 = dt3.AddTicks(7);
Console.WriteLine("dt3: " + dt3.ToString("yyyy-MM-dd-HH:mm:ss.fffffff"));

using (TestE1Context ctx =
      new TestE1ContextFactory().CreateDbContext(new string[0]))
{
    //insert into database==========================
    Console.WriteLine("\nInsert into database===============");
    People p1 = new People();
    p1.ID = Guid.NewGuid();
    p1.Name = "Mark";
    p1.TsDatatime = dt1;
    p1.TsDatatime2 = dt1;
    ctx.People.Add(p1);

    People p2 = new People();
    p2.ID = Guid.NewGuid();
    p2.Name = "John";
    p2.TsDatatime = dt2;
    p2.TsDatatime2 = dt2;
    ctx.People.Add(p2);

    People p3 = new People();
    p3.ID = Guid.NewGuid();
    p3.Name = "Rafa";
    p3.TsDatatime = dt3;
    p3.TsDatatime2 = dt3;
    ctx.People.Add(p3);

    ctx.SaveChanges();

    //read from database 1==========================
    Console.WriteLine("\nRead from database 1 - Getting values from EF cache===============");

    foreach (People p in ctx.People)
    {
        Console.WriteLine("Name: " + p.Name + " TsDatatime: " + p.TsDatatime.ToString("yyyy-MM-dd-HH:mm:ss.fffffff")
            + " TsDatatime2: " + p.TsDatatime2.ToString("yyyy-MM-dd-HH:mm:ss.fffffff"));
    }
}

using (TestE1Context ctx =
      new TestE1ContextFactory().CreateDbContext(new string[0]))
{
    //read from database 2==========================
    Console.WriteLine("\nRead from database 2 - Real values from database, because it is new EF context===============");

    foreach (People p in ctx.People)
    {
        Console.WriteLine("Name: " + p.Name + " TsDatatime: " + p.TsDatatime.ToString("yyyy-MM-dd-HH:mm:ss.fffffff")
            + " TsDatatime2: " + p.TsDatatime2.ToString("yyyy-MM-dd-HH:mm:ss.fffffff"));
    }
}

And here is the execution result:

And database state

The loss of accuracy and rounding problems are obvious now from the database table state.

The funny thing is that EF will, in the first database query, try to fill data from EF Cache, and it creates the illusion that we have better accuracy than we really have.

C# Example 2

The second demo application will show the rounding problem that happens in database queries but not in LINQ queries in memory.

Here is our program.

Here is the code of our Example 2.

using ExampleE2;
using ExampleE2.TestE1DB;

Console.WriteLine("Hello from ExampleE2");

//creating timestamps===============
Console.WriteLine("\nCreating timestamps===============");
//since datetime is rounded to a precision of a milisecond
//we will create timestams of same precision
// 2023-05-07-11:12:13.126
DateTime dt1 = new DateTime(2023, 5, 7, 11, 12, 13, 126);
Console.WriteLine("dt1: " + dt1.ToString("yyyy-MM-dd-HH:mm:ss.fffffff"));

using (TestE1Context ctx =
      new TestE1ContextFactory().CreateDbContext(new string[0]))
{
    //insert into database==========================
    Console.WriteLine("\nInsert into database===============");
    People p1 = new People();
    p1.ID = Guid.NewGuid();
    p1.Name = "Pelf";
    p1.TsDatatime = dt1;
    p1.TsDatatime2 = dt1;
    ctx.People.Add(p1);

    ctx.SaveChanges();

    //read from database 1==========================
    Console.WriteLine("\nRead from database 1 - Getting values from EF cache===============");

    List<People>? listPeople = ctx.People.Where(p=> p.TsDatatime == dt1).ToList();

    People? p1Found = listPeople.Where(p => p.TsDatatime == dt1).FirstOrDefault();

    if (p1Found != null)
    {
        Console.WriteLine("Found! Timestamps match!");
        Console.WriteLine("Name: " + p1Found.Name + " TsDatatime: " + p1Found.TsDatatime.ToString("yyyy-MM-dd-HH:mm:ss.fffffff")
            + " TsDatatime2: " + p1Found.TsDatatime2.ToString("yyyy-MM-dd-HH:mm:ss.fffffff"));
    }
    else
    {
        Console.WriteLine("Not Found!");
    }
}


using (TestE1Context ctx =
      new TestE1ContextFactory().CreateDbContext(new string[0]))
{
    //read from database 2==========================
    Console.WriteLine("\nRead from database 2 - Real values from database, because it is new EF context===============");

    //this is real query execution, so rounding of dt1 is happening on the database side
    List<People>? listPeople = ctx.People.Where(p => p.TsDatatime == dt1).ToList();

    //this is LINQ query in memory, so there is no rounding of dt1
    People? p1Found = listPeople.Where(p => p.TsDatatime == dt1).FirstOrDefault();

    if (p1Found != null)
    {
        Console.WriteLine("Found! Timestamps match!");
        Console.WriteLine("Name: " + p1Found.Name + " TsDatatime: " + p1Found.TsDatatime.ToString("yyyy-MM-dd-HH:mm:ss.fffffff")
            + " TsDatatime2: " + p1Found.TsDatatime2.ToString("yyyy-MM-dd-HH:mm:ss.fffffff"));
    }
    else
    {
        Console.WriteLine("Not Found! Problem with rounding of timestamps caused mismatch.");
    }

    Console.WriteLine("Show list so to see what is there===============");
    foreach (People p in listPeople)
    {
        Console.WriteLine("Name: " + p.Name + " TsDatatime: " + p.TsDatatime.ToString("yyyy-MM-dd-HH:mm:ss.fffffff")
            + " TsDatatime2: " + p.TsDatatime2.ToString("yyyy-MM-dd-HH:mm:ss.fffffff"));
    }
}

And here is the execution result.

And database state

We already saw that EF will, in the first database query, try to fill data from EF Cache, and it creates the illusion that rounding works fine.

But, in 2nd case, rounding is a problem. In real query execution, rounding of dt1 is happening on the database side. But, when executing the LINQ query in memory, there is no rounding of dt1. The result is timestamps mismatch, resulting in the record can not be found in the collection, although the collection was “designed” to contain that particular record.

That is the “WEIRED” behavior that I saw first in my code. Because rounding happening/not happening always, my records were not found. There is always a workaround; you can try to anticipate possible rounding and search for records in the interval of [timestamp, timestamp+4miliseconds].

Conclusion

The best solution is to avoid having SqlServer data type “datetime” in your database and always use “datatime2” which does not have accuracy and rounding problems.

But, if you have a legacy database with a lot of SqlServer data type “datetime” and you can not migrate all tables/columns to the “datetime2” data type, you need to be careful about situations like in the above examples.

References

• https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver16 
• https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver16 
• https://www.codeproject.com/Articles/5361520/Entity-Framework-7-Database-First-Using-EFCorePowe