SQL Server: DateTime.Now and similar functions should be translated differently for datetime2 #36616
Closed as duplicate of#26780
Description
await using var context = new BlogContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();
_ = await context.Blogs.Where(b => b.DateTime > DateTime.Now).ToListAsync();
public class BlogContext : DbContext
{
public DbSet<Blog> Blogs { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseSqlServer("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging();
}
public class Blog
{
public int Id { get; set; }
public DateTime DateTime { get; set; }
}We map .NET DateTime to the higher-precision SQL Server datetime2 type:
CREATE TABLE [Blogs] (
[Id] int NOT NULL IDENTITY,
[DateTime] datetime2 NOT NULL,
CONSTRAINT [PK_Blogs] PRIMARY KEY ([Id])
);The query, on the other hand, translates to GETDATE() which returns the older, no-longer-recommended datetime:
SELECT [b].[Id], [b].[DateTime]
FROM [Blogs] AS [b]
WHERE [b].[DateTime] > GETDATE()We should be translating to SYSDATETIME() instead (see this docs on the different datetime functions).