Operand type clash: text is incompatible with datetime2

• AMCiti

  SSC-Addicted

  Points: 496

  More actions

  November 30, 2020 at 8:19 pm

  Go to Answer

  #3815304

  Dear Group:

  I am not sure what is causing this, and having a terrible time debugging and hoping someone might notice what I am missing.  If I take the actual SELECT * FROM OPENQUERY(......) and run it, I have no problem and the data is returned, so there isn't anything wrong with the SELECT statement, but when I add the "INSERT INTO....." part, I get the following error:

  Operand type clash: text is incompatible with datetime2

  I tried to use NULL values for the dates, but didn't help.  Google hasn't solved this either for me, so I thought I would write and ask all of you.  Anyone know what I am missing?

  We are trying to pull data from an Oracle server and insert it into our SQL Server tables with the code below using a Linked Server on the SQL Server.

  insert into [TEMP_Table1] select * from 
  openquery(Oracle_PROD, '
  SELECT DISTINCT
  A.REQUEST_ID,
  A.STATUS_ID,
  A.CREATED_BY,
  A.CREATE_DATE,
  A.LAST_UPDATED_BY,
  A.LAST_UPDATE_DATE,
  A.IS_LATE,
  A.LATE_DATE,
  A.CANCELLED_BY,
  A.CANCEL_DATE,
  A.CANCEL_COMMENT,
  A.RESOLVE_DATE,
  A.CLOSE_DATE,
  A.REOPEN_DATE,
  '''' AS REQUEST_TYPE,
  '''' AS STATUS_DATE,
  '''' AS PROCESSED_BY,
  '''' AS STATUS_DESC,
  '''' AS "COMMENT_",
  '''' AS FIRST_ACTIONED_DATE,
  '''' AS FIRST_ACTIONED_BY
  FROM REQUEST A
  LEFT JOIN TRAN C ON A.TID = C.TID AND A.AID = C.AID
  WHERE A.STATUS_ID = 1 AND A.TYPE_ID IN (1,2,3) AND A.CREATE_DATE < SYSDATE')

• pietlinden

  SSC Guru

  Points: 63428

  More actions

  November 30, 2020 at 9:10 pm

  Answer

  #3815314

  The error "Operand type clash: text is incompatible with datetime2" sounds like the date columns in Oracle are being interpreted as text, so you might have to use an expression to cast that as a datetime2 column and then append that.

• December 10, 2020 at 7:45 am

  This was removed by the editor as SPAM