Many developers agree on working with dates is more challenging than working with other data types. I recently had to convert some long timestamp strings to short date values. Here are the functions I used:
Original value: 09-MAY-16 10.11.50.596605 AM -04:00
Target value: 09/05/2016
Code:
TO_CHAR(TO_TIMESTAMP_TZ(V_DATE_TIME, 'DD-MON-RR HH:MI:SS.FF AM TZH:TZM'), 'DD/MM/YYYY')
Original value: 09-MAY-16 10.11.50.596605 AM -04:00
Target value: 09/05/2016 10:11 AM
Code:
TO_CHAR(TO_TIMESTAMP_TZ(V_DATE_TIME, 'DD-MON-RR HH:MI:SS.FF AM TZH:TZM'), 'DD/MM/YYYY HH:MI AM')
As you see in the examples above, I first convert the string value in V_DATE_TIME to timestamp data type by using TO_TIMESTAMP_TZ. Then I convert timestamp data type back to string in the new format by using TO_CHAR function.