1

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?

j_deany
  • 67
  • 10

1 Answers1

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.
enter image description here

Koushik Roy
  • 6,868
  • 2
  • 12
  • 33
  • Thanks, but how does this work for a date time with PM, can I add another statement in the Decode function (same as AM)? – j_deany Feb 06 '21 at 12:57
  • 1
    Informatica needs either AM or PM. You do not have to check both. I edited the answer and added test result. – Koushik Roy Feb 06 '21 at 16:01
  • I wasn't able to add the above in a Mapping configuration task so instead I used this, however I'm getting an error "Invalid string converting to Date" Could you please help? DECODE (TRUE, is_date(Extract_LAIB_LastCCVRefreshDate__c, 'YYYY-MM-DD HH:MI:SS AM'), to_char(to_date(Extract_LAIB_LastCCVRefreshDate__c, 'YYYY-MM-DD HH:MI:SS AM')),'YYYY-MM-DD HH24:MI:SS') – j_deany Feb 08 '21 at 11:04
  • I think you have misplaced a ')'. to_char did not have end ')'. . pls use this `DECODE (TRUE, is_date(Extract_LAIB_LastCCVRefreshDate__c, 'YYYY-MM-DD HH:MI:SS AM'), to_char(to_date(Extract_LAIB_LastCCVRefreshDate__c, 'YYYY-MM-DD HH:MI:SS AM'),'YYYY-MM-DD HH24:MI:SS'))` – Koushik Roy Feb 08 '21 at 12:32
  • Still getting the Error message [<> [TO_DATE]: invalid string for converting to Date ... t:TO_DATE(u:DECODE(i:TRUE,i:IS_DATE(u:'2012-04-03 03:43:00 PM',u:'YYYY-MM-DD HH:MI:SS PM'),u:TO_CHAR(t:TO_DATE(u:'2012-04-03 03:43:00 PM',u:'YYYY-MM-DD HH:MI:SS AM'),u:'YYYY-MM-DD HH24:MI:SS'),u:NULL),u:'MM/DD/YYYY HH24:MI:SS.US')]. – j_deany Feb 08 '21 at 12:54
  • is it possible to simplify the logic with IIF - `IIF( is_date(Extract_LAIB_LastCCVRefreshDate__c, 'YYYY-MM-DD HH:MI:SS AM'), to_char(to_date(Extract_LAIB_LastCCVRefreshDate__c, 'YYYY-MM-DD HH:MI:SS AM'),'YYYY-MM-DD HH24:MI:SS')) )`. – Koushik Roy Feb 08 '21 at 14:26
  • Still getting the same Error message is [<> [TO_DATE]: invalid string for converting to Date ... t:TO_DATE(u:IIF(i:IS_DATE(u:'2012-04-03 03:43:00 PM',u:'YYYY-MM-DD HH:MI:SS PM'),u:TO_CHAR(t:TO_DATE(u:'2012-04-03 03:43:00 PM',u:'YYYY-MM-DD HH:MI:SS PM'),u:'YYYY-MM-DD HH24:MI:SS'),u:''),u:'MM/DD/YYYY HH24:MI:SS.US')]. – j_deany Feb 08 '21 at 16:34
  • 1
    Why are you putting `TO_DATE` around `IIF` expression? Do you want a datetime output or a string output? It seems like you need a datetime so couldyou pls use this - `IIF( 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'))` – Koushik Roy Feb 09 '21 at 05:09
  • 1
    Thank you so much your last IFF formula with datetime output worked!! – j_deany Feb 11 '21 at 11:36