1

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
MOR_SNOW
  • 787
  • 1
  • 5
  • 16
  • 5
    `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) –  May 01 '18 at 10:45
  • 1
    This error message might be due to `BirthDate` is being set to default DateTime value 0001-01-01. – Saadi May 01 '18 at 10:48
  • 1
    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 May 01 '18 at 10:57
  • 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 May 01 '18 at 11:00
  • Check the `birthDate` parameter in your controller `AddMember` during debugging, what value does it have? Most likely it does not have what you think it should have. Also, in your model you have specified the `BirthDate` to be of type `DateTime` but then you have this `[Column(TypeName = "datetime2")]` and those 2 are completely different data types. Anyhow, you are getting the error because the value you are trying to save into the db is not in the acceptable range for `datetime`. – CodingYoshi May 01 '18 at 11:03
  • 1
    Your POST method should be `public ActionResult AddMember(xxx model)` where `xxx` is the model you use in the view –  May 01 '18 at 11:07
  • I don't have the dateTime2 annotation anymore. This was just to try the solution that others suggested. I don't know why it says dateTime2 at all, to begin with, I only had/have DateTime. I specified in my post what value the birthDate has – MOR_SNOW May 01 '18 at 11:07
  • Then take it easy with samples. The same method in beginner samples have 2 Create methods. One with no parameters (invoked in first call with GET). Second has an annotation of HttpPost and has a parameter of your model (say your model class is User - Create(User user)-) and this one is the method called when user fills the values and POSTs. – Cetin Basoz May 01 '18 at 11:09

1 Answers1

1

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;
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • You too are mentioning DateTime2 :-) But no such exists? I can't use it, nor find the namespace for it? – MOR_SNOW May 01 '18 at 11:02
  • 1
    It's not a .Net type, it's an SQL Server type. I've edited my answer to provide links to official documentation. – Zohar Peled May 01 '18 at 11:06
  • Thanks @Zohlar Peled. I am wondering. Since I am using EF code first approach, will I have to alter the column everytime I create the DB from scratch? – MOR_SNOW May 01 '18 at 11:17
  • 2
    @MOR_SNOW, no your problem is not there. Datetime would work just fine, unless you have a need to store dates earlier than 1753. – Cetin Basoz May 01 '18 at 11:19