How to fix the datetime2 out-of-range conversion error using DbContext and SetInitializer?

Question

I'm using the DbContext and Code First APIs introduced with Entity Framework 4.1.

The data model uses basic data types such as string and DateTime. The only data annotation I'm using in some cases is [Required], but that's not on any of the DateTime properties. Example:

public virtual DateTime Start { get; set; }

The DbContext subclass is also simple and looks like:

public class EventsContext : DbContext
{
    public DbSet<Event> Events { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Event>().ToTable("Events");
    }
}

The initializer sets dates in the model to sensible values in either this year or next year.

However when I run the initializer, I get this error at context.SaveChanges():

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

I don't understand why this is happening at all because everything is so simple. I'm also not sure how to fix it since there is no edmx file to edit.

Any ideas?

Solution 1

You have to ensure that Start is greater than or equal to SqlDateTime.MinValue (January 1, 1753) - by default Start equals DateTime.MinValue (January 1, 0001).

Solution 2

Simple. On your code first, set the type of DateTime to DateTime?. So you can work with nullable DateTime type in database. Entity example:

public class Alarme
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        public DateTime? DataDisparado { get; set; }//.This allow you to work with nullable datetime in database.
        public DateTime? DataResolvido { get; set; }//.This allow you to work with nullable datetime in database.
        public long Latencia { get; set; }

        public bool Resolvido { get; set; }

        public int SensorId { get; set; }
        [ForeignKey("SensorId")]
        public virtual Sensor Sensor { get; set; }
    }

Solution 3

In some cases, DateTime.MinValue (or equivalenly, default(DateTime)) is used to indicate an unknown value.

This simple extension method can help handle such situations:

public static class DbDateHelper
{
    /// <summary>
    /// Replaces any date before 01.01.1753 with a Nullable of 
    /// DateTime with a value of null.
    /// </summary>
    /// <param name="date">Date to check</param>
    /// <returns>Input date if valid in the DB, or Null if date is 
    /// too early to be DB compatible.</returns>
    public static DateTime? ToNullIfTooEarlyForDb(this DateTime date)
    {
        return (date >= (DateTime) SqlDateTime.MinValue) ? date : (DateTime?)null;
    }
}

Usage:

 DateTime? dateToPassOnToDb = tooEarlyDate.ToNullIfTooEarlyForDb();

Solution 4

Even though this question is quite old and there are great answers already, I thought I should put one more which explains 3 different approaches to solve this problem.

1st Approach

Explicitly map DateTime property public virtual DateTime Start { get; set; } to datetime2 in corresponding column in the table. Because by default EF will map it to datetime.

This can be done by fluent API or data annotation.

1. Fluent API

   In DbContext class overide OnModelCreating and configure property Start (for explanation reasons it's a property of EntityClass class).

   protected override void OnModelCreating(DbModelBuilder modelBuilder)
   {
       //Configure only one property 
       modelBuilder.Entity<EntityClass>()
           .Property(e => e.Start)
           .HasColumnType("datetime2");
   
      //or configure all DateTime Preperties globally(EF 6 and Above)
       modelBuilder.Properties<DateTime>()
           .Configure(c => c.HasColumnType("datetime2"));
   }
   

2. Data annotation

   [Column(TypeName="datetime2")]
   public virtual DateTime Start { get; set; }
   

2nd Approach

Initialize Start to a default value in EntityClass constructor.This is good as if for some reason the value of Start is not set before saving the entity into the database start will always have a default value. Make sure default value is greater than or equal to SqlDateTime.MinValue ( from January 1, 1753 to December 31, 9999)

public class EntityClass
{
    public EntityClass()
    {
        Start= DateTime.Now;
    }
    public DateTime Start{ get; set; }
}

3rd Approach

Make Start to be of type nullable DateTime -note ? after DateTime-

public virtual DateTime? Start { get; set; }

For more explanation read this post

Solution 5

You can make the field nullable, if that suits your specific modeling concerns. A null date won't be coerced to a date that isn't within the range of the SQL DateTime type the way a default value would. Another option is to explicitly map to a different type, perhaps with,

.HasColumnType("datetime2")

View more solutions

Comments

[Alex Angas]

I had left some of my initializer objects without a set date, so it would have been defaulting to DateTime.MinValue.

[eran otzap]

that's not always the case. {1/1/0001 12:00:00 AM} also makes this error emerge

[Mike Devenney]

I did that too ^. Added a custom date field to the asp.net identity ApplicationUser object then forgot to initialize it to something that made sense. : (

[Machado]

In my case, the problem was in the min date, 01/01/0001 was generating the error.

[Anabeil]

how i can check dateTime.minvalue ?

[SIRHAMY]

A little late, but @Anabeil you should be able to just Console.WriteLine(DateTime.MinValue) in the immediate window of VS/linqpad