Came across an error today generated by Entity Framework:

‘The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.’

Typically a datetime2 type can be inserted into a datetime type column provided the value of the datetime2 is between the minimum and maximum values of the datetime type.

  • The date range for a datetime type is
    • January 1, 1753, through December 31, 9999
  • Whilst for a datetime2 type it is
    • January 1,1 AD through December 31, 9999 AD.
If a datetime2 field in EF is created and not set to anything, it defaults to 01/01/0001. The simple example below returns the same error as above.

set nocount on
declare @date datetime = getdate()
declare @date2 datetime2 = getdate()
declare @date2range datetime2 = '0001-01-01 11:28:30.1330000'
print @date2range

DECLARE @MyTableVar table(
datecolumn datetime NOT NULL
)
 insert into @MyTableVar
 values
 (@date),(@date2)

select * from @MyTableVar

insert into @MyTableVar
 values
 (@date2range)
 select * from @MyTableVar
 set nocount off