Informatica Expression Timestamp(6) to Timestamp(0)

Question

Hi can any one please help me In converting Timestamp(6) to Timestamp(0) in Informatica.

My Inputs are like Below, it may be in any of the below formats and It is in String format, This should be converted to Timestamp(0).

            '2015-07-12 14:03:10'

            '2015-07-11 12:12:14.730000'

            '2015-07-10'

The output should be like below,should be like yyyy-mm-dd hh:mm:ss

             '2015-07-12 14:03:10'

             '2015-07-12 12:12:14'

             '2015-07-10 00:00:00'

Answer 1

You need to check each scenario and convert it to date when a match is found:

IIF(IS_DATE(inp_DATE,'YYYY-MM-DD HH24:MI:SS'),TO_DATE(inp_DATE,'YYYY-MM-DD HH24:MI:SS'),
IIF(IS_DATE(inp_DATE,'YYYY-MM-DD HH24:MI:SS.US'),TO_DATE(inp_DATE,'YYYY-MM-DD HH24:MI:SS.US'),
IIF(IS_DATE(inp_DATE,'YYYY-MM-DD'),TO_DATE(inp_DATE,'YYYY-MM-DD'),NULL)))

If you need the date as string format in target you can use TO_CHAR(new_DATE,'YYYY-MM-DD HI24:MI:SS')