Timestamp error

[Ragunathan Gunasekaran]

Hi ,

I am reading a Timestamp column from a flat file but it comes with some default values as '0000-00-00 00:00:00' which i have to convert that as Null before i try to insert them to my oracle database.

The design of job looks like below

Sequential stage ----> Transformer -----> Oracle database(ODBC stage)

In the transformer i have written a derivation to handle this but i am getting a warning while the job runs

If link_name.Column_name =StringToTimestamp('0000-00-00 00:00:00','%yyyy-%mm-%nn %hh:%nn:%ss) Then SetNull() Else link_name.Column_name


I am getting as warning as

Conversion error calling conversion routine timestamp_from_string data may have been lost

Because of this the job is also aborting.Any clue of how to get rid of this Please

[ray.wurlod]

Since 0000-00-00 is not a valid date, the StringToTimestamp() throws an exception. Handle it as an ordinary string in the If..Then..Else construct, and handle the conversion in the Else clause.

[chulett]

You need to convert it only if it is not that value. So first SetNull() on a full string match else do the conversion to a Timestamp.

[Ragunathan Gunasekaran]

As per Ray's comment StringToTimestamp() function was not accepting value as '0000-00-00 00:00:00' and hence read the column as a varchar and performed the Value to Null conversion. Thanks for the advise