SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM in web api

Question

I am trying to build a post method in a web api using c# on asp.net mvc 4.Whenever I post value to this api using fiddler I get the following error:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

I have written the following code in my web API controller.I have also created the data access layer to access the data in database

public HttpResponseMessage PostMsg(MsgModel item)
    {
        if (ModelState.IsValid && item!=null)
        {
            using (dc = new MsgDataContext())
            {
                var dbMsg = new Msg() 
                {
                 Msg_Title= item.Msg_Title,
                 Msg_Date = item.Msg_Date,                    
                };                  
                dc.Msgs.InsertOnSubmit(dbMsg);
                dc.SubmitChanges();// Getting the above error at this line of code

                var response = Request.CreateResponse<MsgModel>(HttpStatusCode.Created, item);
                string uri = Url.Link("DefaultApi", new { id = dbMsg.Msg_Id});
                response.Headers.Location = new Uri(uri);
                return response;
            }
        }
        else
        {
            return Request.CreateResponse(HttpStatusCode.BadRequest);
        }

    }

I am posting the following request body from fiddler by executing the
url http://localhost:65070/api/signup Request Body {
'Msg_Title':"our first msg", 'Msg_Date':"2012/02/23T00:00:00"}

Debug your method I guess `item.Msg_Date` is DateTime.Min. Or you have some additional DateTime fields on `Msg` which you don't fill in. — nemesv, Mar 25 '13 at 11:50

score 0 · Answer 1 · answered Mar 25 '13 at 12:03

0

The error itself tells the solution-

You are assigning DateTime value that is not between these two dates. Just check the date before inserting in the database. If the date is less than 1/1/1753 then pass DateTime.MinValue and if date is greater than 12/31/9999, pass DateTime.MaxValue

answered Mar 25 '13 at 12:03

Microsoft DN

9,706
10
51
71

No, if you do as instructed here you will get this error . I know this for a fact because this is the , seemingly logical , code that I debugged my way to. – LostNomad311 Sep 12 '14 at 01:12

score 0 · Answer 2 · answered Mar 25 '13 at 13:59

0

The date value 'Msg_Date':"2012/02/23T00:00:00" parameter you are passing isn't being parsed properly (as a "known" date string) - it doesn't have a timezone/doesn't match any "known" date format.

See DateTime.TryParse

answered Mar 25 '13 at 13:59

EdSF

11,753
6
42
83

score 0 · Answer 3 · edited May 23 '17 at 11:59

Your problem is that you are parsing a string to a DateTime object. If Msg_Date is a DateTime object C sharp will try an implicit conversion. If that fails null will be returned. But null values for DateTime object in your database are not allowed so this exception appears. Do some debugging before save your entity to database. Find a proper date format because that seems to be your problem. Check this post, or read some documentation.

score -1 · Answer 4 · answered Sep 09 '14 at 17:47

-1

Using SqlTypes.SqlDateTime.MinValue instead of DateTime.MinValue fixes this issue for me . SqlTypes.SqlDateTime.MinValue.Value can be used if you are using DateTime objects in your code already.

answered Sep 09 '14 at 17:47

LostNomad311

1,975
2
23
31

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM in web api

4 Answers4