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??

• Michael Chapman-474993

  Grasshopper

  Points: 13

  More actions

  February 24, 2010 at 4:41 am

  #1124195

  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

  February 24, 2010 at 9:22 am

  #1124338

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

• FV-381972

  SSC Veteran

  Points: 266

  More actions

  March 2, 2010 at 5:26 pm

  #1127473

  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