ORACLE ignores date parameters from SSIS or open query through linked server

Question

ORACLE ignores date parameters from SSIS or open query through linked server

FV-381972

SSC Veteran

Points: 266
More actions
November 24, 2009 at 3:51 pm

#385845

I am stumped as to why I cannot pass date parameters to oracle from ssis using an OLE DB connection or an openquery select through a server link.
If I run this query in Oracle toad
Select *
from Oracle.table
where CREATE_DT > '05-Nov-2009'
I get approximately 308,000 from a 38,000,000 record table.
However running the same query in either SSIS using an OLE DB connection or an openquery select through a server link brings back all 38,000,000 records.
I am using no parameters just the same query with the date hardcoded
To try and get the query to return the correct data I have tried the following syntax with the dates
to_date(to_char(ee.CREATE_DT ,'mm-ddd-yyyy'),'mm-ddd-yyyy') > '05-Nov-2009'
TRUNC(ee.CREATE_DT ) >'05-Nov-2009'
ee.CREATE_DT > to_date('05-Nov-2009')
to_date(to_char( ee.CREATE_DT ,'mm-ddd-yyyy'),'mm-ddd-yyyy') >= to_date('05-Nov-2009'))
Any of these approaches still brings back all the records for the table. It appears as though Oracle does not recognize the date and so ignores it.
Any ideas??

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Michael Chapman-474993 Grasshopper Points: 13 More actions · Answer 1

I had this same problem, after trying various different conversions and comparisons I found this solution works, basically convert both dates to Julian numbers, not very elegant but it works!

WHERE cast(to_number(to_char(event_date ,'J')) as numeric(10,0)) <= cast(to_number(to_char(to_date('14-FEB-2010','DD-MON-YYYY'),'J')) as numeric(10,0))

FV-381972 SSC Veteran Points: 266 More actions · Answer 2

Thanks for this I will give it a try and post if it works.

FV-381972 SSC Veteran Points: 266 More actions · Answer 3

Michael - thanks again this worked with some slight modification.

I have been formatting my SQL data to a string format that I thought Oracle would like so I had dates that looked like 04-Nov-2009 so to get it to work in the code you provided I had to wrap it in a to_date then use the rest of the code you provided as follows:

cast(to_number(to_char(ORA_DATE_dT,'J')) as numeric(10,0)) > cast(to_number(to_char(to_date( '25-Dec-2009'),'J')) as numeric(10,0)) )

It worked! only pulled 1.75 million records instead of 200 million. It did not seem to impact the performance much even though it had all the conversions in the where clause.

Thanks again