CODEWITHSUNDEEP
c#sqllinq
Lemur
Lemur

Reputation: 2665

SqlDateTime overflow when inserting value with DateTime.Now

Lately I have quite odd error while trying to do db.SubmitChanges():

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

The point is, I only use DateTime.Now to set property in my object, and after calling Response.Write(DateTime.Now.ToString()); it shows 17-04-2013 18:03:13 as it should be.

It was not happening earlier, and now the function always breaks. I'm completely clueless - date on my SQL server seems to be ok.

What may cause it?

Edit

I don't think it would help (it just too simple to have any errors IMO), but there's my function:

public bool ReportLogIn(int UserID, string IP, int Succeed ... ) {
    A_UserLoginHistory Report = new A_UserLoginHistory();

    Report.IP = IP;
    Report.UserID = UserID;
    Report.Status = Succeed;
    Report.Date = DateTime.Now; //the only DateTime field
    ...

    try {
        db.A_UserLoginRegistry.InsertOnSubmit(Report);
        db.SubmitChanges();
        return true;
    } catch (Exception e) {
        ErrorLog.AddError(e.ToString());
        return false;
    }
}

Upvotes: 15

Views: 30144

Answers (2)

WiiMaxx
WiiMaxx

Reputation: 5420

actually the problem is SQL DateTime =/= C# Datetime

you need to change 2 things

  • Database change the field type from DateTime to DateTime2

  • Query you need to be explicit

    SqlCommand cmd = new SqlCommand("insertsomeDate", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@newDate", SqlDbType.DateTime2).Value = yourDate; //<- as example

you can find futher informations here,here and here

Upvotes: 5

wizzardmr42
wizzardmr42

Reputation: 1644

Most likely thing is that you have forgotten to initialise a date field - are you sure you've set them all and haven't added a new one? I usually get this when I add a new date field to the DBML as it tries to insert 01/01/0001 00:00:00

If that doesn't help, set a New StringWriter on DB.Log before you do DB.SubmitChanges and examine DB.Log.ToString afterwards (can do in the debugger). This should show you the query and all parameters (at the bottom) so you can see match up what parameter is causing the problem.

Another thing that helps with this kind of problem is using DB.GetChangeSet() to check what records and being inserted/updated before the SubmitChanges call (can't imagine a delete could cause this)

Upvotes: 1

Related Questions