Conversion of a datetime2 data type to a datetime data type results out-of-range value
c#, datetime, entity-framework, orm, sql-server
Solution
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!
Long Answer
The value of `default(DateTime)` is `DateTime.MinValue` (or `new DateTime(1, 1, 1)` or 01/01/0001), which is not a valid SQL `datetime` value.
The lowest valid value for SQL Server datetime is `01/01/1753` due to its use of a Gregorian calendar. SQL Server DateTime2 however supports dates starting at 01/01/0001. Entity Framework by default uses DateTime2 for representing dates, so the generated SQL is implicitly coercing the generated DateTime2 value to a DateTime value on the SQL Server-side.
Problem
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?