Most XML datetime information is represented like the following:
<date>2011-05-23T12:01:51.217+02:00</date> |
So said that, how do you get from that format to a Oracle datetype… The following will help converting you from the W3C datetime towards Oracle datetypes…
WITH datestuff AS (SELECT xmltype('<date>2011-05-23T12:01:51.217+02:00</date>') xmlcol FROM dual ) SELECT to_timestamp_tz(xt.datum,'YYYY-MM-DD"T"HH24:MI:SS.FF9tzh:tzm') AS "TO_TIMESTAMP_TZ" FROM datestuff , xmltable('*' passing xmlcol COLUMNS datum varchar2(35) PATH '/date' ) xt; TO_TIMESTAMP_TZ ----------------------------------- 23-05-11 12:01:51,217000000 +02:00 |
So use the timestamp with timezone function and make sure the format fits while using a varchar2(35).
1 comment
Marco Gralike
9 August, 2011 at 16:32 (UTC 1) Link to this comment
Alternatively if the origin is a TIMESTAMP column and you need to shred the values via the columns section of XMLTABLE you can use
DATUM TIMESTAMP(6) WITH TIME ZONE path ‘/date’
This will result in something like “01-08-11 15:24:04,855000000″