How to convert a long timestamp to a short date in Oracle?

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.

Leave a Reply