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?