Conversion of a datetime2 data type to a datetime data type results out-of-range value

In my SQL Server 2008 database, I had a DateTime column flagged as not nullable, but with a GetDate() function as its default value. When inserting new object using EF4, I got this error because I wasn't passing a DateTime property on my object explicitly. I expected the SQL function to handle the date for me but it did not. My solution was to send the date value from code instead of relying on the database to generate it.

obj.DateProperty = DateTime.now; // C#

Add the below mentioned attribute on the property in your model class.

Attribute = [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
Reference = System.ComponentModel.DataAnnotations.Schema

Initially I forgot to add this attribute. So in my database the constraint was created like

ALTER TABLE [dbo].[TableName] ADD DEFAULT (getdate()) FOR [ColumnName]

and I added this attribute and updated my db then it got changed into

ALTER TABLE [dbo].[TableName] ADD CONSTRAINT [DF_dbo.TableName_ColumnName] DEFAULT (getdate()) FOR [ColumnName]

I ran into this issue on a simple console app project and my quick solution is to convert any possible datetime2 dates to a nullable datetime by running this method:

static DateTime? ParseDateTime2(DateTime? date)
    {
        if (date == null || date.ToString() == "1/1/0001 12:00:00 AM")
        {
            return null;
        }
        else
        {
            return date;
        }
    }

This is certainly not a completely comprehensive method, but it worked for my needs and maybe it'll help others!

In my case, when a NULL value is explicitly assigned for Nullable DateTime column and then you try to save the changes. This error will pop up.

In my case we were casting a Date to a Datetime and we got this error. What happens is that Date has a "more programmer oriented" minimum of 01/01/0001, while Datetime is stuck at 1753

Combine that with a data collection error on our part, and you get your exception!

Adding this code to a class in ASP.NET worked fort me:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Properties<DateTime>().Configure(c => c.HasColumnType("datetime2"));
}

I'm aware of this problem and you all should be too:

https://en.wikipedia.org/wiki/Year_2038_problem

In SQL a new field type was created to avoid this problem (datetime2).

This 'Date' field type has the same range values as a DateTime .Net class. It will solve all your problems, so I think the best way of solving it is changing your database column type (it won't affect your table data).

Check out the following two: 1) This field has no NULL value. For example:

 public DateTime MyDate { get; set; }

Replace to:

public DateTime MyDate { get; set; }=DateTime.Now;

2) New the database again. For example:

db=new MyDb();

Problem with inherited datetime attribute

This error message is often showed when a non-nullable date field has value null at insert/update time. One cause can be inheritance.

If your date is inherit from a base-class and you don't make a mapping EF will not read it's value.

For more information: https://weblogs.asp.net/manavi/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-3-table-per-concrete-type-tpc-and-choosing-strategy-guidelines

I saw this error when I wanted to edit a page usnig ASP.Net MVC. I had no problem while Creating but Updating Database made my DateCreated property out of range!

When you don't want your DateTime Property be Nullable and do not want to check if its value is in the sql DateTime range (and @Html.HiddenFor doesn't help!), simply add a static DateTime field inside related class (Controller) and give it the value when GET is operating then use it when POST is doing it's job:

public class PagesController : Controller
{
    static DateTime dateTimeField;
    UnitOfWork db = new UnitOfWork();

    // GET:
    public ActionResult Edit(int? id)
    {
        Page page = db.pageRepository.GetById(id);
        dateTimeField = page.DateCreated;
        return View(page);
    }

    // POST: 
    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Edit(Page page)
    {
        page.DateCreated = dateTimeField;
        db.pageRepository.Update(page);
        db.Save();
        return RedirectToAction("Index");

    }
}

Check the req format in DB. eg my DB have Default value or Binding (((1)/(1))/(1900))

System.DateTime MyDate = new System.DateTime( 1900 ,1, 1);

For me I have had a Devexpress DateEdit component, which was binded to nullable datetime MSSQL column thru the Nullable model property. All I had to do was setting AllowNullInput = True on DateEdit. Having it "Default" caused that the date 1.1.0001 appeared - ONLY BEFORE leaving the DateEdit - and then I got this conversion error message because of subsequent instructions mentioned above.

For Code First Context:

Go from this

    public DateTime Created { get; set; }

To this. Add the [DatabaseGenerated(DatabaseGeneratedOption.Computed)] attribute.

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime Created { get; set; }

in your model.

Be sure to add this to the top as well

using System.ComponentModel.DataAnnotations.Schema;