While running SSIS etl package via SQL Server agent and filtering with TIME/date (expected date being current date minus 1) against an oracle database in another server, query returns date older than 5 months while the same query executed directly on oracle works perfectly
Below is the query
select
to_char(id) id,
to_char(packno) packno,
to_char(TIME, 'DD-MM-YYYY HH:MI:SS')TIME,
to_char(TYPE) TYPE,
to_char(AMOUNTORIG)AMOUNTORIG,
to_char(AUTHFIID)AUTHFIID,
to_char(ORIGUNIT)ORIGUNIT
from bab.tla
where packno between (to_nchar(sysdate-2, 'YYMMDD')) || '001' and (to_nchar(sysdate-0, 'YYMMDD')) || '017'
and time
between
to_date(to_nchar(trunc(sysdate-1), 'dd-mon-yyyy hh24:mi:ss'),'dd-mon-yyyy hh24:mi:ss') and
to_date(to_nchar(sysdate-1, 'dd-mon-yyyy') || ' 23:59:59','dd-mon-yyyy hh24:mi:ss')
Resolved. Issue is with the staging table accidentally changed to an old table within the flow. While the correct staging table is being truncated at the start of the work flow, the wrong table with older data is adding to the query within the flow