top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Informatica Date/Time Conversion

+1 vote
3,286 views

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)?

posted May 12, 2014 by Madhavi Kumari

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

0 votes

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 May 13, 2014 by Shweta Singh
0 votes

'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')

answer Jun 11, 2014 by Shatark Bajpai
Similar Questions
0 votes

I am loading date fields from file to teradata table. In a file my date format is mm-dd-yyyy and in my teradata table the format is yyyy/mm/dd.

I used the below function for formatting:

To_date(to_char(date field,'mm-dd-yyyy'),'yyyy/mm/dd')
But all my records are going in rejected records. When I checked session log my date field was mentioned as invalid date.

Please help on this.

+2 votes

I want to display the time stamp in following format '2011-04-22 10:41:57.000' to date as '04/22/2011' but when it convert it to following form TO_DATE(TO_CHAR(Date), 'MM/DD/YYYY HH24:MI:SS') it is displaying as null.

I am planning to use substring after the conversion

Can someone please tell me where i am going wrong?

+3 votes

How to calculate the Monday date of a week using the week number in Informatica?!

+2 votes

I have two sessions I a workflow like below

workflow1->session1->session2

I have a join_date column in a table in Mapping1 , in session1. I want to pick this join_date value and pass to mpping2/session2

If join date value changes in the table in session1 then the same value should pick and pass to session2. I will use this date value in a query in session2 .

Please suggest how to achieve this?

0 votes

In informatica i receive dates from flat files in the format of dd-mm-yyyy and dd/mm/yyyy i need to convert all date to one format i.e dd-mm-yyyy using any expression and push into target ,so no rows gets rejected.how to proceed with them?

...