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 => 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.

  • 2
    I don't understand datetime type. I know DATE, TIMESTAMP , INTERVAL data types. In this manual Oracle uses 'datetime' for a DATE or one of the TIMESTAMP data types , but there is not somethings like a DATETIME data type. if you have a column of DATE or TIMESTAMP data type, then they dont have a format. So what kind of data type does your source have? – miracle173 Jul 19 '15 at 1:35
  • 1
    The string '4/22/2015 12:02:56 AM' does not represent a datetime in the 'd/mm/yyyy hh:mm:ss' format. Ther is no month represented by 22. – miracle173 Jul 19 '15 at 1:39
  • 1
    ok, then you want to convert them in strings that represents the date in an iso8601 format. this strings you send to your postgres session that will transform them to the appropriate postgres datetime data type. So you can use one of the given answers? – miracle173 Jul 19 '15 at 1:52
  • 2
    if you do a 'select COLNAME from TABLENAME' on the DATE column COLNAME of table TABLENAME an this returns values like '4/22/2015 12:02:56 AM' this does not mean that there are DATE values of this format stored in the table but that your default conversion for the DATE data type to the string data type in your session is 'm/DD/YYYY HH:MI:SS AM'. It does not mean that they are stored in this format. – miracle173 Jul 19 '15 at 2:01
  • 2
    both Oracle and PostgreSQL have a DATE and TIMESTAMP data types. But you cannot read it directly in an Oracle session and write it in a PostgreSQL session. Because there internal representation of this data is completely different. You have to convert it to a string when reading it in the Oracle session and convert it from a string when writing in the postgres session. – miracle173 Jul 19 '15 at 2:07

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:

FROM dual
  • Isn't this casting to a string? I need it casted to a datetime. – Kurt Mueller Jul 19 '15 at 1:14
  • Note that I got an error when I included .ff3 ORA-01821: date format not recognized 01821. 00000 - "date format not recognized" *Cause: *Action: – Matthew Dec 17 '20 at 0:29
  • @Matthew, what is the command you use? The command in my answer will never raise such an error. – Wernfried Domscheit Dec 17 '20 at 6:51
SQL> alter session set nls_timestamp_format = 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"';

Session altered.

SQL> select cast (sysdate as timestamp) from dual;

  • 4
    This is only correct when you local time zone is UTC! Note, if the user changes his local time zone then the display is still ....Z - which is wrong. Time zone independent format is 'YYYY-MM-DD"T"HH24:MI:SS.ff3TZH:TZM' – Wernfried Domscheit Jul 18 '15 at 17:54

Not the answer you're looking for? Browse other questions tagged or ask your own question.