Search

OakieTags

more tags

Who's online

There are currently 0 users and 30 guests online.

Recent comments

Affiliations

Home

HOWTO: Convert from W3C XML DateTime format to Oracle Timezone

Submitted by marco on Mon, 2011-05-23 12:59

Most XML datetime information is represented like the following: 

#66cc66;"><date#66cc66;">>#cc66cc;">2011#66cc66;">-05#66cc66;">-23T12:01:#cc66cc;">51.217#66cc66;">+02:00#66cc66;">date#66cc66;">>

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…

#993333; font-weight: bold;">WITH datestuff #993333; font-weight: bold;">AS
 #66cc66;">(#993333; font-weight: bold;">SELECT xmltype#66cc66;">(#ff0000;">'2011-05-23T12:01:51.217+02:00'#66cc66;">) xmlcol 
    #993333; font-weight: bold;">FROM dual
 #66cc66;">)
#993333; font-weight: bold;">SELECT to_timestamp_tz#66cc66;">(xt#66cc66;">.datum#66cc66;">,#ff0000;">'YYYY-MM-DD"T"HH24:MI:SS.FF9tzh:tzm'#66cc66;">) 
       #993333; font-weight: bold;">AS #ff0000;">"TO_TIMESTAMP_TZ"
#993333; font-weight: bold;">FROM   datestuff 
#66cc66;">,      xmltable#66cc66;">(#ff0000;">'*'
                passing xmlcol
                #993333; font-weight: bold;">COLUMNS
                  datum varchar2#66cc66;">(#cc66cc;">35#66cc66;">) PATH #ff0000;">'/date'
                #66cc66;">) xt;
 
TO_TIMESTAMP_TZ
#808080; font-style: italic;">-----------------------------------
#cc66cc;">23#66cc66;">-05#66cc66;">-#cc66cc;">11 #cc66cc;">12:01:#cc66cc;">51#66cc66;">,#cc66cc;">217000000 #66cc66;">+02:00

So use the timestamp with timezone function and make sure the format fits while using a varchar2(35).