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

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:

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")

Solution 5:

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