SqlDateTime overflow when inserting value with DateTime.Now

Question

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;
    }
}

could it be DMY/MDY related? i.e. would 12-04-2013 18:03:13 be ok? — paul, Apr 17 '13 at 16:11
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
@mellamokb - it's inside a function that operates only on this database table object — Lemur, Apr 17 '13 at 16:15
What is the current DATEFORMAT of the sql server? Use `DBCC USEROPTIONS` — nathan_jr, Apr 17 '13 at 16:16
@NathanSkerl - hmm... It shows `dateformat: mdy`, `datefirst: 7`, but the database is hosted by an external provider, and I can't tell if the setting were the same earlier - only that it was working before. Is it possible that those settings has changed somehow? That would explain this error. — Lemur, Apr 17 '13 at 16:21
@PabloLemurr yea, hard to say if/when they changed. Check this question: http://stackoverflow.com/questions/2455115/accepted-date-format-changed-overnight — nathan_jr, Apr 17 '13 at 16:42
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
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
Try formatting the date in ISO 8601 format ("yyyy-MM-dd HH:mm:ss"). — David R Tribble, Apr 17 '13 at 21:24
@sgmoore - re-creating database model in both VS and SQL server doesn't work, too — Lemur, Apr 18 '13 at 08:35
@outcoldman - date sent to my DB is in the formatt I provided in the post, and it doesn't work, but when I insert the row manually from SMSS it works fine. — Lemur, Apr 18 '13 at 08:36
@Loadmaster - it does not help - I tried several datetime formats, including this one you provded, but it's not that. I'm now almost pretty sure that it's something wrong with the SQL server. — Lemur, Apr 18 '13 at 08:41

score 5 · Accepted Answer · edited May 23 '17 at 10:34

5

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

edited May 23 '17 at 10:34

Community

1
1

answered Apr 18 '13 at 09:10

WiiMaxx

5,322
8
51
89

Changing the type to `datetime2` helped, but I still have no idea why it stopped working for `datetime` – Lemur 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

score 1 · Answer 2 · answered Apr 17 '13 at 20:00

1

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)

answered Apr 17 '13 at 20:00

wizzardmr42

1,634
12
22

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. – Lemur Apr 18 '13 at 08: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 08: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 09:04

SqlDateTime overflow when inserting value with DateTime.Now

2 Answers2

Linked