Informatica Date/Time Conversion

Question

In one of the requirment informatica fetching data from flat file as source file and insert records into a temporary table temp of DB2 database. Flat file has one column as datetime datatype (YYYY/MM/DD HH:MM:SS). However, informatica fetching this column as string datatype (Since Informatica date format is different from this column & DB2). So before loading into temp table of DB2 database, I need to convert back this column into Datetime format.

With Expresion transformation, I can do this but I dont know how? To_date conversion function (TO_DATE(FIELD, 'YYYY/MM/DD HH:MM:SS')) is there but it will take care of date only (YYYY/MM/DD). Its not taking care of time (HH:MM:SS) and because of this records are not inserting into temp table.

How can I convert datetime from String datatype to DB2 datetime format (YYYY/MM/DD HH:MM:SS)?

Answer 1

You tried to use the month format string (i.e. MM) for the minutes part of the date.

You need to use MI:

TO_DATE(FIELD, 'YYYY/MM/DD HH:MI:SS')

Answer 2

'YYYY/MM/DD HH:MM:SS' is not a correct format (MI should for minute in spite of MM), probably its a typo mistake due to that you are facing issue.

You can use this syntax - TO_DATE(FIELD, 'YYYY/MM/DD HH:MI:SS')