xDisruptor xDisruptor - 8 months ago 73
SQL Question

How to store DateTime.Now on a DateTime2(Precision=0) column in a way that milliseconds are omitted ("zeroed-out")

I'm using entity framework to setup a table using fluent-api configuration:

Property(g => g.DateTime).IsRequired().HasColumnType("datetime2").HasPrecision(0);

The table does indeed get created successfully:

CREATE TABLE [dbo].[Foo] (
[DateTime] DATETIME2 (0) NOT NULL,

The precision of the datetime2 column has been set to 0 as you can see. I thus expect the retrieved date-time values to not include milliseconds at all, aka dates should look like '13 March 2016 18:35:37.0000'. However the retrieved dates always include milliseconds. Here's the code I'm using:

var dbcontext = new ApplicationDbContext(); //foo table is empty
dbcontext.Foo.Add(new Entry { DateTime = DateTime.Now });
var date = dbcontext.Foo.First().DateTime; //this should be identical to DateTime.Now above except for milliseconds which should be set to zero right?

How can I achieve the desired effect without resorting to zeroing-out milliseconds manually (via C# code either before insertion or after retrieval)?


If you need just date and time without any milliseconds, use smalldatetime MS SQL type instead. It has accuracy of 1 seconds.

If for some reason you want to have datetime2 in the database, there's no automatic way you can achieve desired behavior. You can create a (calculated) property MyDateTimeWithoutMs that get and set the correct value for the database connected property.

    internal DateTime databaseDateTime { get; set; }
    public DateTime MyDateTimeWithoutMs 
            return databaseDateTime.DateTimeWithoutMs();
            databaseDateTime= value.ToDateTimeWithoutMs();

In your model mapping add ignore for calculated property and map the database property to the actual column name.

public class EntryMap : EntityTypeConfiguration<Entry >
    public Entry Map()
        Property(t => t.databaseDateTime)
        Ignore(t => t.MyDateTimeWithoutMs );