invalid datetime format
<p>I have a question about powercenter message code: RR-4035. I have a mapping in which i am using a sql override query, this error is in sql override. This mapping is failing with an error, </p>

<blockquote>
  <p>'[IBM][CLI DRIVER]CLIO113E SQLSTATE 22007:An invalid datetime format
  was detected, that is an invalid string representation or value was
  specified'.</p>
</blockquote>

<pre><code>&gt; Database driver error: 
</code></pre>

<p>Function name:Fetch
SQL STMNT: </p>

<pre><code>select s.employee_record_id,s.employee_id,s.record_origin,
cnt.employee_contract_id,cnt.employee_contract_efctv_dt,cnt.employee_contract_term_dt,club.employee_club

from

employee_main_info s

inner join

(select 

employee_id,record_origin,employee_contract_term_dt,employee_contract_efctv_dt
from employee_perm

union
select 

employee_id,record_origin,employee_contract_term_dt,employee_contract_efctv_dt
from employee_temp
) cnt on s.employee_id=cnt.employee_id,

employee_club_data club

where

club.employee_id=s.employee_id

and (cnt.employee_contract_efctv_dt &lt;=sysdate or cnt.employee_contract_efctv_dt&lt;'2016-01-01') 
and s.employee_record_term_dt&gt;sysdate;
</code></pre>

<p>native error code= -99999</p>

<p>I have tried everything, my previous mappings have run fine with the same datetime formats but this one is failing. One thing i have noticed is that if i remove all the transformations in between the source qualifier and target the mapping succeeds and data gets loaded to target, but as soon as i put any lookups or expressions between source qualifier and target except a pass through expression, the mapping fails again.</p>

<p>Any suggestion, any help regarding this is appreciated.</p>