I´ve got this in a INSERT statment to MSSQL 2008
System.Data.SqlClient.SqlException: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
- 5The problem occurred when a used Entity Framework to INSERT a System.DateTime into my SQL2008 DB. I changed datatype in DB to datetime2 and now everything runs smoothly.Glenn– Glenn2009-03-17 20:01:27 +00:00Commented Mar 17, 2009 at 20:01
- 5The problem had it´s begining with a logical error and the DateTime was never initialized ie I tried to insert 0001-01-01 00:00:00.0000000Glenn– Glenn2009-03-17 20:04:45 +00:00Commented Mar 17, 2009 at 20:04
- 4This one got me when I didn't initialize a DateTime in EF4 because I had a default value of GetDate() in the database.user1228– user12282010-03-22 13:53:55 +00:00Commented Mar 22, 2010 at 13:53
Add a comment |
5 Answers
SQLServer's datetime datatype is a much smaller range of allowed values than .net datetime datatype. SQLServer's datetime type basically supports the gregorian calendar, so the smallest value you can have is 1/1/1753. In 2008 SQLServer added a datetime2 datatype that supports back to year 1 (there was no year 0). Sounds like you're trying to insert a datetime value that's before 1/1/1753 into a datetime (not datetime2) SQLServer column
1 Comment
Tobias J
Voted up for answering what the user really needed to know instead of the literal question :)
Defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.
Comments
Could it be that your database table has a "DATETIME" or "SMALLDATETIME" column and you're trying to insert an out-of-range date?? DATETIME covers 1753-1-1 through 9999-12-31, while SMALLDATETIME covers 1900-1-1 through 2079-6-6 only.
The new SQL Server 2008 DATETIME2 data type will cover 0001-1-1 through 9999-12-31.
Marc
Comments
From technet:
Defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.
I had to check because I thought datetime2 had some relation with varchar2. Apparently, no relation at all.
Put your code so we can guess what caused the problem.
Comments
I got this error when my database column was created as NOT NULL and I specifically specified a Nullable = true on my DateTime Property in my ADO.Entity framework EntityType.
To fix it I make the Nullable property = (None)
