Storing DateTimeOffset value without milliseconds in SQL Server using EF Core

Question

Say I create a DateTimeOffset value like this:

var dt = DateTimeOffset.UtcNow;

And I want to store this value in SQL Server using EF Core code-first. How do I remove the milliseconds portion, so that the value stored in SQL Server becomes 2020-09-08 14:51:00 +00:00 instead of 2020-09-08 14:51:00.4890984 +00:00?

I don't know about EF core but in SQL Server you can have your column defined as a `DateTimeOffset(0)` which means no fractional seconds. — Zohar Peled, Sep 08 '20 at 15:07
as Zohar said, you should configure the property on your model by specifying the DateTimeOffset(0) type, either through attributes, or through the fluent configuration — ESG, Sep 08 '20 at 15:12
Feel free to post the solution as a self answer, so that other users might benefit from your experience. — Zohar Peled, Sep 09 '20 at 09:20

score 1 · Answer 1 · answered Sep 08 '20 at 15:09

1

I agree with @Zohar Peled. Define your data as it is supposed to be.

other than that, In c# you can truncate date in this way:

dt.AddTicks( - (dt.Ticks % dt.TicksPerMilliseconds));

this post has more general approach.

answered Sep 08 '20 at 15:09

Derviş Kayımbaşıoğlu

28,492
4
50
72

1

Thanks for the response and the reference. I ended up using Zohar/ESG's approach to configure it at the database level. – Mike Hawkins Sep 08 '20 at 15:18

Jonathan Ramos · Answer 2 · 2022-05-03T19:03:45.400

1

Based on @Zohar suggestion using Data Attributes on your Model binded to EF:

    [Column(TypeName = "datetimeoffset(0)")]
    public DateTimeOffset Date { get; set; }

edited May 03 '22 at 19:03

answered Aug 12 '21 at 23:04

Jonathan Ramos

1,921
18
21

score 0 · Answer 3 · answered Sep 08 '20 at 23:26

A simple way is to use a value converter.

The code could look like this (using the answer from How to truncate milliseconds off of a .NET DateTime for the actual conversion logic):

public class YourContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<YourEntity>()
            .Property(e => e.YourProperty)
            .HasConversion(
                v => v.AddTicks(-(dateTime.Ticks % TimeSpan.TicksPerSecond));,
                v => v);
    }
}

Of course you can also explicitly define and then reuse the value converter and you can also apply it to all properties of a certain type like 'DateTimeOffset', by iterating over the entities and their properties via ModelBuilder.

Storing DateTimeOffset value without milliseconds in SQL Server using EF Core

3 Answers3