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. Knows a thing or two about Pokémon. Knows too much about SQL Agent. 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