When I select data from Oracle to SQL Server via linked server I get exception

SELECT CreationDate FROM OPENQUERY (ORCLS, 'Select CreationDate from ADMIN.CV_TMP_Subscrs_Stage4_List')

Msg 8114, Level 16, State 8, Line 7 Error converting data type DBTYPE_DBTIMESTAMP to datetime.

If I cast date field in Oracle to varchar, reconvert it to datetime in sql server and fetch data I see

Different date formats between sql server and oracle

As far as I understand, Oracle formats date as "dd.MM.yyyy". In order to SQL Server to understand that I need to:

  1. Convert all date/datetime fields in queries to varchar
  2. Reconvert varchar to datetime in sql server side with specific format.

Seems like shooting cannon at sparrows. Maybe there is an easier way?

  • 1
    The error is likely caused by Oracle dates that are out of range for datetime. What driver are you using for Oracle? If you are using the old MSDAORA, try the latest Oracle OleDB driver instead. But honestly using Oracle's TO_CHAR to convert to varchar is not a bad option. – David Browne - Microsoft Dec 18 '17 at 17:19
  • Yes I'm using MSDAORA. I think I need to invest into installing Oracle OleDB driver. – nikita Dec 18 '17 at 17:28
  • Here's a walkthough: blogs.msdn.microsoft.com/dbrowne/2013/10/02/… – David Browne - Microsoft Dec 18 '17 at 17:32
  • 1
    MSDAORA is deprecated for ages. You should really go for new Oracle OleDB provider. – Wernfried Domscheit Dec 18 '17 at 19:07
  • Regarding "Oracle formats date as "dd.MM.yyyy". ", Oracle formats the date however the client requests. A DATE column is binary, not character, and included the time, down to the second. If the client does not specify a format, it will use the database setting for NLS_DATE_FORMAT, which may or may not be what you think. See edstevensdba.com/oracle-data-types/… for a more thorough explanation. – EdStevens Dec 18 '17 at 22:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.