question

kkran-5951 avatar image
0 Votes"
kkran-5951 asked EchoLiu-msft commented

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

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?

sql-server-generalsql-server-transact-sql
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Are you sure that the type if date?

Check this query:

 SELECT *
 FROM OPENQUERY(ORACLE, 'SELECT nullif(CONVERSION_RATE_DATE, ''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''')

0 Votes 0 ·

Hi -Thanks.

I am getting an error : ORA-00932: inconsistent datatypes: expected DATE got CHAR

0 Votes 0 ·

If you know Oracle, what is your opinion about this error?

It seems that you can use to_date or cast. Maybe try this:

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



0 Votes 0 ·
Show more comments

Do you have anyupdate?

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @kkran-5951

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi - Thanks, I am getting the below error :

"ORA-00906: missing left parenthesis"

0 Votes 0 ·

Following Stefan Hoffmann’s suggestion,I did a simple search,the date range is -4713 and +9999 on oracle.However,the date range of datetime2 on tsql is 0001-01-01 to 9999-12-31. In addition,I can't find ‘0/0/0000’ date format on oracle.

0 Votes 0 ·
StefanHoffmann avatar image
0 Votes"
StefanHoffmann answered

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).

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.