String to date time in informatica

Question

I am preparing an Informatica mapping

Source:- Oracle Target :- Teradata

There are 2 columns in the mapping Source column name : INX_DATE(string) and UPX_DATE (string) Target column name : INX_DATE(date/time) and UPX_DATE (date/time)

Source column contains something like this

UPX_Date:-08-FEB-16 12.00.23.826315000 AM +00:00 INX_Date:- 08-FEB-16 12.00.23.826315000 AM EUROPE/BELGRADE

Now the issue is how will I get this to my target which is date/time and should be MM/DD/YYYY HH:MM:SS

Answer 1

You can substring the first 31 characters and then convert it to date.

TO_DATE(SUBSTR(INX_DATE,1,31),'DD-MON-YY HH12:MI:SS.NS AM')

As far as I know, PowerCenter Date/Time cannot store the time zone information. If you need that, you may need to adjust the time separately.