DSXchange

Hello there,

I have a fixed width file coming with one of the field( Char(8) ) having a date format
eg:20111122 and it has some empty values too.I have to load it to Oracle table as Timestamp which is NULLABLE and when i imported the table definitions to datastage it is imported as Timestamp with scale=6 and no length defined

I am doing a TrimLeadingTrailing of that column and used the below code

if Column='' then SetNull() else StringToTimestamp(Column:'00:00:00',"%yyyy%mm%dd %hh:%nn:%ss")

But, this is throwing me an error
Warning:::1.APT_CombinedOperatorController,0: Conversion error calling conversion routine timestamp_from_string data may have been lost
Fatal:::2.oracle_enterprise_stage,0: Failure during execution of operator logic.
Fatal:::3.oracle_enterprise_stage,0: Fatal Error: Invalid time

I tried replacing the oracle enterprise stage with a sequential stage for output, then the job ran successfully with a warning
APT_CombinedOperatorController,0: Conversion error calling conversion routine timestamp_from_ustring data may have been lost

I just tweeked with a peek stage and i have seen that the column is generating ******************* value. I have seen the input file and no value for that column has any such records

You've omitted the space at the beginning of the constant time portion.

So, instead of
if Column='' then SetNull() else StringToTimestamp(Column:'00:00:00',"%yyyy%mm%dd %hh:%nn:%ss")

I need to specify
if Column='' then SetNull() else StringToTimestamp(Column:' ': '00:00:00',"%yyyy%mm%dd %hh:%nn:%ss")

Tried the above but the job aborted with Fatal errors below
Oracle_enterprise_stage,0:Failure during execution of operator logic.
Oracle_enterprise_stage,0:Fatal Error: Invalid time
main_program: Unexpected exit status 1

Use the following code..
If (IsNull(Column) Or Column = '') Then SetNull() Else StringToTimestamp(Column:' ' :'00:00:00',"%yyyy-%mm-%dd %hh:%nn:%ss")

Can you show a small variety of examples of your input?