How to filter on SSIS when querying from an Oracle source on the Oracle generic Date type

user_9862230 0
Viewed 118

I am trying to extract data from an Oracle Database using SSIS to a Microsoft Destination. I would like to filter the Extract on a generic Oracle date type of dd-mon-rr HH24:MI:SSXFF. When i run the query on SSIS the dates appear in the format MM/DD/YYYY. As a result I cannot get a result. Wonder if anyone can help me to filter it correctly.

SELECT CAST(GRA_DATE AS DATE) as GRA_DATE,
       CAST(VERIFIED_DATE AS DATE) as VERIFIED_DATE,
       VERIFIED_QTY,
       EXTRACT_DATE,
       SEQ_NBR
  FROM ORDERS
 WHERE TO_DATE(TO_CHAR(TRUNC(VERIFIED_DATE)), 'DD/MM/YYYY') =
       TO_DATE('07/08/2018', 'dd/MM/YYYY')

I expect retained data to be filtered to the 7th of August 2018. Currently there is no result in SSIS but I get the result in the actual Oracle source

ssis oracle sql
1 Answers

seems verified_date is of date type( trunc is being applied ). So, extra conversion (to char after to date) is not needed.

Just query as

SELECT ....
  FROM ORDERS 
 WHERE trunc(verified_date) = date'2018-08-07'

where date'YYYY-MM-DD' conforms to ISO 8601 standard.

user_58413060
Related