Oracle 9: Convert date from mm/dd/yyyy hh:mm:ss format to iso8601 formatted datetime Note: I only have read access to this database.

I need to convert an Oracle 9 date type from d/mm/yyyy hh:mm:ss format to the iso8601 standard. Like the example below:

4/22/2015 12:02:56 AM =&gt; 2015-04-22T04:02:56.000Z

Below is a a query that will convert an Oracle date type to a iso8601-formatted string. I need an is8601 formatted date.

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


Thanks for any and all help.



EDIT: The end goal is to grab these dates and insert them as datetimes into a postgres database that powers a rails app.
You should use SYSTIMESTAMP instead of SYSDATE, in order to get fractional seconds (which you desire as I assume).

SYSTIMESTAMP returns data type TIMESTAMP WITH TIME ZONE, thus you don't have to specify explicitly.

So it is:

SELECT
    TO_CHAR(
        SYSTIMESTAMP AT TIME ZONE 'UTC',
        'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"'
    )
FROM dual