Having issues with DateTime (Conversion of a datetime2 datatype results in out of range)

Question

I am getting this error:

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

The view that interacts with users has the following relevant code:

@Html.LabelFor(model => model.BirthDate)
@Html.TextBoxFor(model => model.BirthDate, new { size = "50", @type = "date", @class = "AddMemberControls" })

The texbox where the user has to input date, says "mm/dd/yyyy" (and it shows a calender where the user can pick a date)

The controller method looks like this:

public ActionResult AddMember(string firstName, string lastName, DateTime birthDate, int age, string sportType)
{            
    var member = new Member() { FirstName = firstName, LastName = lastName, Age = age, SportType = sportType, BirthDate = birthDate };

    coloContext.Members.Add(member);
    coloContext.SaveChanges();  

    return View();
}

And model:

[Column(TypeName = "datetime2")]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
public DateTime BirthDate { get; set; }

If for example the user chooses the date 05/01/18, then when debugging, and checking the birthdate parameter, it says 01-May-18 12:00:00 AM

I guess the issue is that it writes "May", and the database doesn't know how to save that?

I see there is a lot about this issue on the internet, and several attempts on a solution, but unfortunately, neither has worked for me yet.

I have tried:

Add annotation with column typename

[Column(TypeName = "datetime2")]

Making Datime nullabe in my model (Which several solutions suggested). But this solution does not make much sense to me. Especially when I know that this parameter can not be null

public DateTime? BirthDate { get; set; }

Tried to parse the DateTime, hoping that it will get rid of the "May". If possible though, I'd like the date to be saved in that format.

DateTime test = DateTime.parse(birthDate.ToString())

Some suggested changing to DateTime2, but there is no object called DateTime2? I tried anyway, but got an error on this

public DateTime2 BirthDate { get; set; }

And a few other solutions that I forgot.

01-May-18 12:00:00 AM is just how its being displayed to you. The error message means you posting back a value that is not in the valid range, and I assume your DateTime birthDate is 01/01.0001 (and why in the world are you not binding to your model) — user3559349
This error message might be due to BirthDate is being set to default DateTime value 0001-01-01. — Saadi
With a value like 05/01/2018 you wouldn't get that error. However instead of binding to your model you are passing parameters. Maybe you have the error there, you didn't supply the code that sets those parameters. Binding to model would be the easiest thing to do. — Cetin Basoz
I am not sure what you guys mean with "Bind to model"? I have only started working with asp.net mvc less than 2 weeks ago, so I'm only int the beginner process. — MOR_SNOW
Your POST method should be public ActionResult AddMember(xxx model) where xxx is the model you use in the view — user3559349

Zohar Peled Zohar Peled · Accepted Answer · 2018-05-01T10:51:40

From the error message it's quite clear that the data type in the actual database table is not DateTime2 but DateTime.

These data types differ in acceptable range (among other things).

The acceptable range of DateTime2 is 0001-01-01 through 9999-12-31
(January 1,1 CE through December 31, 9999 CE)
while The acceptable range of DateTime is only 1753-01-01 through 9999-12-31
(January 1, 1753, through December 31, 9999). (bonus reading: Why?)

There are many reasons why you should stop working with DateTime and use Date, Time, or DateTime2 instead - here is a good blog post about it.

So to fix the problem, change the data type of the database column from DateTime to DateTime2. This can be done by a simple alter table dml statement:

ALTER TABLE YourTable
    ALTER COLUMN col DateTime2;

Having issues with DateTime (Conversion of a datetime2 datatype results in out of range)

1 Answers