Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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


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; }
    }

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();

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