How to convert flat file DateTime to Timestamp format in Informatica Cloud Services

Question

Trying to convert the date value coming in from csv flat file (2012-04-03? 3:43:00 PM) into Normal timestamp value (YYYY-MM-DD HH24:MM:SS - without AM or PM).

DECODE (TRUE,
is_date(Extract_LAIB_LastCCVRefreshDate__c, 'YYYY-MM-DD HH:MM:SS AM'), to_date(Extract_LAIB_LastCCVRefreshDate__c, 'YYYY/MM/DD HH24:MM:SS'),
is_date(Extract_LAIB_LastCCVRefreshDate__c, 'YYYY-MM-DD HH:MM:SS PM'), to_date(Extract_LAIB_LastCCVRefreshDate__c,'YYYY/MM/DD HH24:MM:SS'), 
ERROR( 'NOT A VALID DATE') )

Above function gives me invalid expression error, how can I fix this?

question from:https://stackoverflow.com/questions/66065472/how-to-convert-flat-file-datetime-to-timestamp-format-in-informatica-cloud-servi

Answer 1

Pls use this to convert to date-time first. Then you can convert to anything in char format.

1. v_temp_dttm1 =  DECODE (TRUE,
is_date(Extract_LAIB_LastCCVRefreshDate__c, 'YYYY-MM-DD HH:MI:SS AM'), to_date(Extract_LAIB_LastCCVRefreshDate__c, 'YYYY-MM-DD HH:MI:SS AM')
)

2. out_temp_dttm1 = to_char(v_temp_dttm1,'YYYY/MM/DD HH24:MI:SS')

I tested above formula using informatica and here is the output. i used mm/dd/yyyy as output format though.