Conversion of a datetime2 data type to a datetime data type results out-of-range value
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!
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
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))