The conversion of a datetime2 data type to a smalldatetime data type resulted in an out-of-range value.\r\nThe statement has been terminated [duplicate]

Question

I made a small MVC4 app and made database on my local SQL 2012 server. "imported" that data from my 2012 moved it to my cheap shared host as production with sql 2008. everything worked fine. made some changes to non-aspmembership tables and deleted tables from sql 2008 and reimported. not for some reason i get this error

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

if i try to register an account. i tried chaning all of the smalldatetimecolumns to datetime2 columns but for some reason the same error occurs even though there are no smalldatetimes?

Does anyone have any ideas? thank you.

edit: people of the future - i am unsure why this fixed it, but i recreated the tables in SQL express 2008 instead of SQL 2012 and then moved them over. worked fine.

Answer 1

From the look of the error, your database is using SmallDateTime as date type. Its min value is 1900-Jan-01. Assuming your entity is something like below

  public class Game
  {
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid GameId { get; set; }

    [Required]
    public DateTime GameTime { get; set; }
  }

Your GameTime value is not nullable, so when you don't set any value for it, it will always be the DateTime.Min which is 0000-Jan-01. This is out of range of SmallDateTime, but it is in range of DateTime2. So EF will try to pass a DateTime2 SQL type to the SQL server. Because your database is using SmallDateTime, you will get the error shown in your question.

To resolve the issue, you have following options that I can think of:

• make the field nullalbe. (you have to always check if your input dates are within the SmallDateTime range.)

• or change the date type to DateTime2 in SQL server

• or force EF to use DateTime2 data type when creating database. The code will be like something below. (This method should inside of your context class.)

  protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity()
            .Property(t => t.GameTime )
            .HasColumnType("datetime2");
    }

• Write a converter helper to set your DateTime property min value to match smalldatetime in your application.

• Write a trigger in the database to convert DateTime2 (from application end, but datetime2 in database end) to smalldatetime.

Hopefully, I have understood your question properly, and my answer helps.