current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 8 down vote favorite

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;
    }
}
share|improve this question
1  
possible duplicate of stackoverflow.com/questions/3399061/… –  Dhaval Marthak Apr 17 '13 at 16:11
4  
could it be DMY/MDY related? i.e. would 12-04-2013 18:03:13 be ok? –  paul Apr 17 '13 at 16:11
4  
Remember that db.SubmitChanges() applies every change since you last called SubmitChanges() or created the context. You can determine what all changes are involved by looking at db.GetChangeSet(). Any date/time column on any affected object could be the cause - for instance, if any datetime comes through with a default of DateTime.MinValue, it is out of range for SQL Server. –  mellamokb Apr 17 '13 at 16:11
1  
Things to check. Check your model and database are correct (If they are using dates then Linq should generate TSQL using Sql parameters which means it should not really matter how the server is configured). If you are inserting new records, check there are no other date/datetime columns which you haven't initialized. Check that there is no other Sql executed when you make your changes eg triggers etc. –  sgmoore Apr 17 '13 at 17:00
1  
Try to run SQL Profiler to get all calls to database. This will show you when you have this exception and why. –  outcoldman Apr 17 '13 at 18:46

2 Answers 2

up vote 2 down vote accepted

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

share|improve this answer
    
Changing the type to datetime2 helped, but I still have no idea why it stopped working for datetime –  Lemurr Apr 18 '13 at 10:34
    
@PabloLemurr maybe there was an update somewhere which results in your problem –  WiiMaxx Apr 18 '13 at 11:52
    
DateTime2 supports a wider range of Dates (0001-01-01 through 9999-12-31), which would suggest the date passed to sql actually is out of range for DateTime and probably DateTime.MinValue). –  sgmoore Apr 18 '13 at 12:53
up vote 1 down vote

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)

share|improve this answer
    
The date sent to my DB is in the format I provided in the post, and it doesn't work, but when I insert the row manually from SMSS it works fine. And I have this onyl DateTime field there that started to cause the problem. –  Lemurr Apr 18 '13 at 8:37
    
Can you change it to use ISO config (YYYY-MM-DD)? I think that is more reliable for date values –  wizzardmr42 Apr 18 '13 at 8:49
    
+1, forgetting to initialize a DateTime field is the most common way of getting a date outside the SqlDateTime range. –  Joe Apr 18 '13 at 9:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.