conversion of a datetime2 data type to a datetime data type exception with dates after 1753/1/1

Question

I have added a property to my Customer Model, created a migration and updated the database. The field created in DB is datetime. here is my Model code

public DateTime BirthDate { get; set; }

here is the view that is actually a form where I insert date and other fields and submit the form.

<div class="form-group">
    @Html.LabelFor(m => m.Customer.BirthDate)
    @Html.TextBoxFor(m => m.Customer.BirthDate, "{0: dd-MM-yyyy}", new { @class = "form-control" })
</div>

and here is the Action in the controller

     public ActionResult Save(Customer customer)
    {
        if (customer.Id == 0)
        {
            _context.Customers.Add(customer);
        }
        else
        {
            var customerInDb = _context.Customers.Single(c => c.Id == customer.Id);
            customerInDb = customer;
        }
        _context.SaveChanges();
        return RedirectToAction("Index", "Customers");
    }

I have papulated the BirthDate field with 23/04/1976 and debug the application and check the value of customer.BirthDate which is 23/04/1976 12:00:00 AM. The date is note before 1753/1/1 but I m receiving the said exception.

I have also tried the following: Make the field nullable by

public DateTime? BirthDate { get; set; }

the exception is gone but the date is not being saved to the database.

Removed the formate "{0: dd-MM-yyy}" from the view but in vain.

Inserted dates in different formats e.g. 23-Apr-1952, 1985-12-01 and 1987/1/2 but didn't work.

Visual Studio 2013 and Database is LocalDb of visual studio.

Answer 1

The fact that the error goes away by making the property nullable gives the answer. If a value was being set, then you would get exactly the same error as when the field was not nullable.

Therefore, the value is not being set, which stores a null if the field is nullable (confirmed by you saying it doesn't store the value - it's storing null).

So for a not-nullable property, when it isn't set, it will have the default value for a dot net DateTime, which is DateTime.MinValue, which would be valid if stored in an SQL server datetime2 but not in a datetime, where you get the 1753 error.

By the way, I'm assuming this LocalDb is basically SQL server, based on the 1753 error. For the record, in SQL server you should be using datetime2: DateTime2 vs DateTime in SQL Server

But this field is actually a Date, so you should be using the date SQL type.

So in summary, I suspect the BirthDate value in the Customer passed to the Save method has the value DateTime.MinValue. Now you need to work out why... (but that's a different question)

P.S. Are you using dd-MM-yyy on purpose? Should there be an extra y?

Answer 2

The actual problem lies in both DateTime viewmodel property and this TextBoxFor helper:

@Html.TextBoxFor(m => m.Customer.BirthDate, "{0: dd-MM-yyy}", new { @class = "form-control" })

Depending on current culture setting, this will generate plain input with specified date format which may not matched with current culture setting in server, which causing default model binder to ignore its value and uses DateTime.MinValue as default value (because it's not a nullable type), which doesn't fit for datetime column type in SQL that has minimum value of 1753-01-01 (the equivalent type of System.DateTime in T-SQL is datetime2).

The most recommended setup to create input helper for DateTime property is using DataTypeAttribute set to DataType.Date and DisplayFormatAttribute to specify the format string:

[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:dd/MM/yyyy}", ApplyFormatInEditMode = true)]
public DateTime BirthDate { get; set; }

Then, use either EditorFor or TextBoxFor with type="date" attribute to generate date input:

@* TextBoxFor *@
@Html.TextBoxFor(model => model.Customer.BirthDate, new { @class = "form-control", type = "date" }) 

@* EditorFor *@         
@Html.EditorFor(model => model.Customer.BirthDate, new { htmlAttributes = new { @class = "form-control" } })

Note: The same setup like above also apply for Nullable<DateTime>/DateTime? property.

Additionally you should check for IsValid property of ModelState before using SaveChanges() and return the same view when validation failed:

[HttpPost]
public ActionResult Save(Customer customer)
{
    if (ModelState.IsValid)
    {
        if (customer.Id == 0)
        {
            _context.Customers.Add(customer);
        }
        else
        {
            var customerInDb = _context.Customers.Single(c => c.Id == customer.Id);
            customerInDb = customer;
        }
        _context.SaveChanges();
        return RedirectToAction("Index", "Customers");
    }
    else
    {
        // validation failed, show the form again with validation errors
        return View(customer);
    }
}

Related issue: ASP.NET MVC date not saving when posting