Menu
I've got a datatable with 5 columns, where a row is being filled with data then saved to the database via a transaction.
While saving, an error is returned:
The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value
It implies, as read, that my datatable has a type of DateTime2 and my database a DateTime; that is wrong.
The date column is set to a DateTime like this:
new DataColumn("myDate", Type.GetType("System.DateTime"))
Question
Can this be solved in code or does something have to be changed on a database level?
478
Short Answer This can happen if you do not initialize a value to a DateTime field; the field does not accept NULL values, and it's a value type, so the default value of the non-nullable DateTime type will be used. Setting the value fixed it for me!
The DateTime2 is an SQL Server data type, that stores both date & time together. The time is based on the 24 hours clock. The DateTime2 stores the fractional seconds Up to 7 decimal places (1⁄10000000 of a second). The Precision is optional and you can specify it while defining the DateTime2 column.
The main difference is the way of data storage: while in Datetime type, the date comes first and then time, in Datetime2, 3 bytes, in the end, represents date part!
DATETIME2 has a date range of "0001 / 01 / 01" through "9999 / 12 / 31" while the DATETIME type only supports year 1753-9999. Also, if you need to, DATETIME2 can be more precise in terms of time; DATETIME is limited to 3 1/3 milliseconds, while DATETIME2 can be accurate down to 100ns.
This can happen if you do not assign a value to a DateTime field when the field does not accept NULL values.
That fixed it for me!
119
Both the DATETIME and DATETIME2 map to System.DateTime in .NET - you cannot really do a "conversion", since it's really the same .NET type.
See the MSDN doc page: http://msdn.microsoft.com/en-us/library/bb675168.aspx
There are two different values for the "SqlDbType" for these two - can you specify those in your DataColumn definition?
BUT: on SQL Server, the date range supported is quite different.
DATETIME supports 1753/1/1 to "eternity" (9999/12/31), while DATETIME2 supports 0001/1/1 through eternity.
So what you really need to do is check for the year of the date - if it's before 1753, you need to change it to something AFTER 1753 in order for the DATETIME column in SQL Server to handle it.
Marc
39
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
