SQL : ORACLE Linked server : Error converting data type DBTYPE_DBTIMESTAMP to datetime2.

Question

Hi Everyone: I am using SQL server version 2017

If I run this query below

SELECT *

FROM OPENQUERY(ORACLE, 'SELECT CONVERSION_RATE_DATE FROM CS_ESTIMATE_DETAILS where LAST_UPDATE_DATE > ''2021-04-05''

and LAST_UPDATE_DATE <= ''2021-04-07''')

This is the error : Error converting data type DBTYPE_DBTIMESTAMP to datetime2.

The error is because in ORACLE it is entered in format ‘0/0/0000’, I know this is not a valid date but that’s how it is entered and the Datatype is 'date'

As this is not a valid date and also SQL server has a limitation where it doesn’t accept date values with 0000.

---

I converted the value to Varchar

SELECT *

FROM OPENQUERY(ORACLE, 'SELECT Cast(CONVERSION_RATE_DATE as varchar(26)) as Converteddate FROM CS_ESTIMATE_DETAILS where LAST_UPDATE_DATE > ''2021-04-05''

and LAST_UPDATE_DATE <= ''2021-04-07''')

It gives me future date : Please see below

'4712-01-01 00:00:00'

In SQL the datatype is datetime2(7)

---

Need your inputs on how to get the value ‘0/0/0000’ from Oracle or convert into NULL value?

Answer 1

Hi @kkran ，

Try:

SELECT *  
FROM OPENQUERY(ORACLE, 'SELECT iif(cast(CONVERSION_RATE_DATE as varchar)=''0/0/0000'',null,CONVERSION_RATE_DATE ) as CONVERSION_RATE_DATE   
FROM CS_ESTIMATE_DETAILS   
where nullif(LAST_UPDATE_DATE, ''0/0/0000'')   
between ''2021-04-05'' and ''2021-04-07''')  

Or:

SELECT *  
FROM OPENQUERY(ORACLE, 'SELECT nullif(cast(CONVERSION_RATE_DATE as varchar), ''0/0/0000'') as CONVERSION_RATE_DATE   
FROM CS_ESTIMATE_DETAILS   
where nullif(LAST_UPDATE_DATE, ''0/0/0000'')   
between ''2021-04-05'' and ''2021-04-07''')  

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

The error is not due to the used format, but the Oracle DATE type has a different range (January 1, 4712 BC to ..) then DATETIME2 (0001-01-01 to 9999-12-31).