Search code examples
asp.netsql-server-2008-express

Out of range exception while convert datetime2 to datetime data type

I've got an exception while updating a datetime column on SQL Server 2008 Express:

eventinfo.StartDateTime = DateTime.ParseExact(start, 
                                              "MM/dd/yyyy hh:mm tt", 
                                              CultureInfo.InvariantCulture);
db.Entry(eventinfo).State = EntityState.Modified;
db.SaveChanges();

Exception:

The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.The statement has been terminated.

Any idea how to avoid this exception?

Solution

  • The domain of Sql Server's date/time data types is as follows:

    • datetime2 (100-nanosecond precision) 1753-01-01T00:00:00.0 — 9999-12-31T23:59:59.9999999.
    • datetime (millisecond precision...sort of) 1753-01-01T00:00:00.000 — 9999-12-31T23:59:59.997
    • smalldatetime (1-second precision) 1900-01-01T00:00:00 — 2079-06-06T23:59:59

    The CLR DateTime struct has 100-nanosecond precision as well. It is a count of 100-second ticks since its epoch (0001-01-01T00:00:00). Its domain is 0001-01-01T00:00:00 — 9999-12-31T23:59:59.9999999.

    You will notice the extended domain of the CLR DateTime as compared to SQL Server's datetime2.

    The default value of the CLR's DateTime is its epoch (0001-01-01T00:00:00). Any value prior to 1753-01-01T00:00:00 will throw an out of range exception.

    In all likelyhood, your DateTime value is somehow not getting initialized or DateTime.ParseExact() is getting handed junk data, parsing it successfuly and winding up with a date/time prior to 1753-01-01.