SQL SERVER ERROR: "The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value." (informix to sql server)

Question

I have a sql server linked server to access an informix database, and I'm using openquery to run the queries, and then insert the results in a sql server table.

The problem is that when I run a code like:

INSERT INTO table_name
( date, comments, user )
SELECT f.date
     , f.comments
     , f.user
  FROM OPENQUERY( LINKED_SERVER_NAME, 
                  'select date
                        , description as comments
                        , user_name as user
                     from tsperson' 
                 ) AS f

I got this error:

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

I've already tried to cast but that doesn't worked.

I've searched here and in Google for some answer but not founded.

So, can someone help me?

This might help - http://stackoverflow.com/questions/7386360/the-conversion-of-a-datetime2-data-type-to-a-datetime-data-type-resulted-in-an-o — MusicLovingIndianGirl, Nov 06 '15 at 12:38
Paddy answer was more explained and has very clear alternatives to me to how to solve the problem. Thanks, anyway. — Smaily Carrilho, Nov 06 '15 at 13:00

score 2 · Accepted Answer · edited Dec 25 '17 at 05:12

It would seem that the date field in your table that you are inserting into is using a datetime datatype. You are receiving data in a datetime2 format that is out of the range of a valid datetime type.

You may have to update the table so that date is a datetime2 type, or filter the incoming data to remove dates that you cannot support.

datetime date rage: January 1, 1753, through December 31, 9999

datetime2 date range: 0001-01-01 through 9999-12-31

See https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql

SQL SERVER ERROR: "The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value." (informix to sql server)

1 Answers1