Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Conversion of a datetime2 data type to a datetime data type results out-of-range value

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?

like image 478
Gerbrand Avatar asked Aug 26 '09 00:08

Gerbrand


People also ask

How do you fix the conversion of a DATETIME2 data type to a datetime data type resulted in an out of range value?

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!

What is a DATETIME2 data type?

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.

What is the difference between DATETIME2 and datetime?

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!

Is DATETIME2 better than datetime?

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.


2 Answers

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!

like image 119
andyuk Avatar answered Sep 16 '22 14:09

andyuk


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

like image 39
marc_s Avatar answered Sep 17 '22 14:09

marc_s