conversion of a datetime2 data type to a smalldatetime EF6 MVC5

Question

I am using MVC5 with EF6 code first. I have a table with a Date(Datum) column in both tabels(Kommunication and Itemannounce) with smalldatetime type and when I try to set the date with System.DateTime.Now in the column I get the following error:

The conversion of a datetime2 data type to a smalldatetime data type resulted in an out-of-range value.The statement has been terminated.

I am aware of byte sizes and tried the following solution in my context:

  protected override void OnModelCreating( DbModelBuilder modelBuilder )
        {
        var annons = modelBuilder.Entity<Itemannounce> ( );
        annons.Property ( f => f.Datum ).HasColumnType ( "smalldatetime" );
        var komm = modelBuilder.Entity<Kommunication> ( );
        komm.Property ( f => f.Date ).HasColumnType ( "smalldatetime" );
        base.OnModelCreating ( modelBuilder );
        }

I still get same error. I tried to set the columns to NOT NULL without any luck. I am realy stuck in several days without to find a way out of this. Does anyone have a solution I have not tried?

score 0 · Answer 1 · edited May 23 '17 at 11:47

The minimum value for smalldatetime is 1900-01-01

The minimum value for datetime2 is 0000-01-01

Presumably, what is happening here is that we are passing a datetime2 value into a smalldatetime column. When datetime2 not been supplied and it is NON NULL then it will default to it's minimum value of 0000-01-01. This is well below the floor for smalldatetime. It is out of bounds. Inserting a value outside of the range of smalldatetime would yield the error message you are recieving.

The conversion of a datetime2 data type to a smalldatetime data type resulted in an out-of-range value.The statement has been terminated.

There are many ways to solve this. I would suggest updating the model to use datetime2.

 protected override void OnModelCreating( DbModelBuilder modelBuilder )
        {
        var annons = modelBuilder.Entity<Itemannounce> ( );
        annons.Property ( f => f.Datum ).HasColumnType ( "datetime2" );
        var komm = modelBuilder.Entity<Kommunication> ( );
        komm.Property ( f => f.Date ).HasColumnType ( "datetime2" );
        base.OnModelCreating ( modelBuilder );
        }

If you don't want to change the model then NULL checking could work as well. This similar question should give you a few ideas.

I tried All other options in previous posts without luck :( – Esmael May 13 '17 at 19:51 — Esmael, May 13 '17 at 19:51

conversion of a datetime2 data type to a smalldatetime EF6 MVC5

1 Answers1