Date Conversion Examples: Oracle Date <=> XML ISO8601 Format

Introduction:

Some examples of Date Conversion from Oracle PL/SQL to and from XML Standard Date (ISO8601 format).

 

Examples:

Convert XML Date to Oracle Date Format:

-- CONVERT XML DATE TO ORACLE PL/SQL
SELECT TO_DATE('2017-08-24T12:11:06Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')
FROM DUAL;

 

Convert XML Date to Oracle System Timestamp Format:

SELECT TO_TIMESTAMP('1985-02-07T00:00:00.000Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')
FROM DUAL;
 
SELECT TO_TIMESTAMP('2017-08-24T12:11:06Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')
FROM DUAL;
 
 

Convert Oracle Date to XML ISO8601 Format:

-- CONVERT ORACLE DATE TO XML ISO8601 FORMAT
SELECT
    TO_CHAR(
        SYSTIMESTAMP AT TIME ZONE 'UTC',
        'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"'
    )
FROM DUAL;
 

  

Convert Oracle Date to XML ISO8601 Format with Time Zone Details:

SELECT SYSDATE,
TO_CHAR((FROM_TZ(TO_TIMESTAMP(TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS PM'), 'YYYY-MM-DD HH:MI:SS PM') ,'AMERICA/NEW_YORK') AT TIME ZONE 'UTC'),'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')
"ISO8601"
FROM DUAL

 

  

Keywords:

Date Conversion, Format, Timestamp, Systimestamp, XML Date, ISO8601, Date Function, PL/SQL, Query, SQL, SQLPLUS