conversion of a datetime2 data type to a smalldatetime EF6 MVC5
asp.net-mvc-5 entity-framework-6 sql type-conversion
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?
Popular Answer
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.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
