Data type conversion in Informatica

0
votes

I have a requirement where the target column data type is date(ex- 30-Apr-14 i.e DD-MON-YY).

In informatica the default data type is date/time. So the source is sending the date data in the format MM/dd/yyyy hh:mm:ss. So when I am trying to insert this data into target it rejects.

How do I convert this source data from date/time to date,so I can finally have the output as DD-MON-YY and will be able insert into target table.

Thanks! Ashim

2
informatica
What is the error? Could you edit your question to include some code we could try to debug? - Adam
It does not error out. Informatica read the data from source , processes. everything good until the end. The process is successful but all the source data are rejected. As my target table allows null values for the date column, it just updates null. So basically nothing is loaded. - Ashim Dash

2 Answers

0
votes

Use TO_CHAR(fieldname,'DD-MON-YYYY')

Where fieldname is your current date port and the expression is in a string port which itself will write to your date column

0
votes

Date/time datatype has no format. If you process your data as date/time and send it to target using date, the connector you're using does the job for you. (NOTE: it usually is a number that is then shown in some readable format)

So, either:

  • your target is using date datatype (and no conversion is needed). In this case the error seems to have a different source, or
  • your target does expect a format, as it's a string with some check constraint.

Make sure which scenario you're dealing with.