Convert XSD date xs: dateTime to Oracle date

Question

I need to convert the date from this format:

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

for an Oracle date .

I use this in PL / SQL: to_date ('date here', 'yyyymmdd'), is there a way to convert this while storing timezone information?

thanks

Answer 1

Oracle dates do not have time zone information. Instead, you need to use the TIMESTAMP data type.

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 that there is a difficult problem with T in the XSD record. This throws an ORA-01858 exception because it is not a valid format in Oracle. I am sure there is a workaround, but currently it is slipping away from me.

---

Well, one way is to use the SUBSTR () function, which separates the two parts of the timestamp, as Bob shows. But there has to be a more elegant way.

---

It probably doesn't qualify as “elegant”, but as a string, we can use the 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 efforts that Oracle has implemented in XMLDB, it is rather annoying that there is no finer solution.

---

"I do not understand how you are -05: 00."

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

Answer 2

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.

Answer 3

Here is an example of how to convert this to DATE and TIMESTAMP WITH TIME ZONE data types. Note that with the DATE type, time zone information is lost (when converting 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 fun, I added the code as an example to infer the time zone from the string, and then added the time zone to local time to get GMT / UTC.

Share and enjoy.