Converting a XSD date xs:dateTime to an Oracle Date

Question

Hello, I need to convert a date from this format:

2002-10-10T12:00:00-05:00 (xs:dateTime as defined in XML)

to an Oracle date.

I'm used to using this in PL/SQL: to_date('date here', 'yyyymmdd'), is there a way to convert this while keeping the time zone info?

Thanks

Answer 1

Oracle dates don't have timezone information. You'll need to use a TIMESTAMP datatype instead.

It works something like this:

SQL> desc tz
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 TS                                                 TIMESTAMP(6) WITH TIME ZONE
 TNOW                                               TIMESTAMP(6) WITH TIME ZONE

SQL> insert into tz
  2  values (1
  3          , to_timestamp_tz('2002-10-10 12:00:00-05:00'
  4                           , 'YYYY-MM-DD HH24:MI:SSTZH:TZM')
  5          , systimestamp)
  6  /

1 row created.

SQL> select * from tz
  2  /

        ID
----------
TS
---------------------------------------------------------------------------
TNOW
---------------------------------------------------------------------------
         1
10-OCT-02 12.00.00.000000 -05:00
23-AUG-10 17.37.06.502000 +01:00


SQL>

Note, there is the tricky issue of the T in the XSD notation. That hurls a ORA-01858 exception, because it's not a valid format in Oracle. I'm sure there is a workaround, but it currently escapes me.

---

Well, one workaround is to apply SUBSTR() function sto split open the two parts of the timestamp, as Bob shows. But there ought to be a more elegant way.

---

It probably doesn't qualify as "elegant" but as it's a string we can use a substitution function to get rid of the annoying T:

SQL> insert into tz
  2  values (2
  3          , to_timestamp_tz(translate('2003-10-10T12:00:00-05:00', 'T', ' ')
  4                   , 'YYYY-MM-DD HH24:MI:SSTZH:TZM')
  5          , systimestamp)
  6  /

1 row created.

SQL> select * from tz
  2  /

        ID
----------
TS
---------------------------------------------------------------------------
TNOW
---------------------------------------------------------------------------
         1
10-OCT-02 12.00.00.000000 -05:00
23-AUG-10 17.37.06.502000 +01:00

         2
10-OCT-03 12.00.00.000000 -05:00
23-AUG-10 17.53.37.113000 +01:00


SQL>

But given all the effort Oracle have put into XMLDB it is rather annoying that there isn't a tidier solution.

---

"I dont understand how you get -05:00."

In my original sample I use a format mask of 'YYYY-MM-DD HH24:MI:SS-TZH:TZM'. This interprets the - in the time zone as a separator not a minus sign. Consequently it returned +05:00. I have since corrected my code sample to remove that last dash. Now the timezone is correctly rendered as -05:00. Sorry for any confusion.

Answer 2

Here's an example of how to convert this to DATE and TIMESTAMP WITH TIME ZONE data types. Note that with the DATE type the time zone information is lost (in the conversion from TIMESTAMP WITH TIME ZONE):

declare 
  strDate     VARCHAR2(32767);
  tzDate      TIMESTAMP WITH TIME ZONE;
  dtDate      DATE;
  nTimezone   NUMBER;
  dtDate_GMT  DATE;
begin
  strDate := '2002-10-10T12:00:00-05:00';

  dtDate := TO_TIMESTAMP_TZ(SUBSTR(strDate, 1, 10) ||
                            SUBSTR(strDate, 12, 8) || ' ' ||
                            SUBSTR(strDate, 20, 6), 'YYYY-MM-DDHH:MI:SS TZH:TZM');
  tzDate := TO_TIMESTAMP_TZ(SUBSTR(strDate, 1, 10) ||
                            SUBSTR(strDate, 12, 8) || ' ' ||
                            SUBSTR(strDate, 20, 6), 'YYYY-MM-DDHH:MI:SS TZH:TZM');
  nTimezone := TO_NUMBER(SUBSTR(strDate, 20, 3)) +
                 (TO_NUMBER(SUBSTR(strDate, 24, 2)) / 60);
  dtDate_GMT := dtDate - ((INTERVAL '1' HOUR) * nTimezone);

  dbms_output.put_Line('dtDate=' || dtDate);
  dbms_output.put_Line('dtDate=' || TO_CHAR(dtDate, 'YYYY-MM-DD HH24:MI:SS'));
  dbms_output.put_line('tzDate=' || tzDate);
  dbms_output.put_line('tzDate=' || TO_CHAR(tzDate, 'YYYY-MM-DD HH24:MI:SS TZH:TZM'));
  dbms_output.put_line('nTimezone=' || nTimezone);
  dbms_output.put_Line('dtDate_GMT=' || TO_CHAR(dtDate_GMT, 'YYYY-MM-DD HH24:MI:SS'));
end;

---

Just for the fun of it I added some code to the example to pull the timezone out of the string, and then added the timezone to the local time to obtain GMT/UTC.

Share and enjoy.

Answer 3

A short answer:

SQL> select to_timestamp_tz('2002-10-10T12:00:00-05:00','yyyy-mm-dd"T"hh24:mi:sstzh:tzm')
  2    from dual
  3  /

TO_TIMESTAMP_TZ('2002-10-10T12:00:00-05:00','YYYY-MM-DD"T"HH24:MI:SSTZH:TZM
---------------------------------------------------------------------------
10-OCT-02 12.00.00.000000000 PM -05:00

1 row selected.

Regards, Rob.