c# conversion of a datetime2 data type to a datetime data type

Question

I've got a datatable with 5 columns, that is being filled with data. From here I'm going to the database and save the whole datatable setting it into an transaction.

While saving, I'm getting an error that says "conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value."

As I understand it good, it says that my datatable has a type of datetime2 and my database a datetime?

How can I solve this matter? Has it to be in my code or on database level?

In my datatable the date column is set like this: new DataColumn("myDate", Type.GetType("System.DateTime"))

Answer 1

The easiest thing would be to change your database to use datetime2 instead of datetime. The compatibility works nicely, and you won't get your errors.

You'll still want to do a bunch of testing...

The error is probably because you're trying to set a date to year 0 or something - but it all depends on where you have control to change stuff.

Rob

Answer 2

What kind of dates do you have in the column?

Do all of them fit within the range of the type?

---

As an aside, the correct way to get a Type object for the DataColumn constructor is the typeof keyword, which is orders of magnitude faster.

Therefore, to create the column, you should write

new DataColumn("myDate", typeof(DateTime))

Answer 3

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 support 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