How to handle high precision dates in Informatica? the ones with milliseconds.

Whenever you import a source from Oracle with a timestamp field, Informatica automatically imports that field as date with precision of 26 and scale 6. This means that any digits in the milliseconds range will get truncated while reading or writing to the table/file.

This means losing the intended precision of data resulting in incorrect comparisons in the transformation process.

The way to resolve this is to increase the precision of the source or target definition to precision 29 and scale 9 after the source/target is imported into Informatica. This will handle the digits in milliseconds without converting them to all zeros.

In addition to this the timestamp field for which you need high precision values needs to be transformed using the correct format in to_timestamp(column_name,’MM-DD-YYYY HH24:MI:SS:FF6′) function in the source qualifier query override.

Join the Conversation

2 Comments

  1. Hi Raghav,

    I have a XML SQ, in its XSD one of the data element is datetime format. And has value like 2010-08-21T11:04:09.540Z, but the SQ is unable to read the milli seconds part which is 540. Date format in session property is MM/DD/YYYY HH24:MI:SS.US. (FYI: Z is the timezone)

    DO I need to change any other settings ?

    Thanks
    Adr

Leave a comment

Leave a Reply to Adr Cancel reply

Your email address will not be published. Required fields are marked *