Range Values of DATETIME vs DATETIME2

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

Author: Richie Lee

Full time computer guy, part time runner. Full time Dad, part time blogger. Pokémon Nut. Writer of fractured sentences. Maker of the best damn macaroni cheese you've ever tasted.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s