CODEWITHSUNDEEP
sqlxmloracle-databaseoracle11g
thursdaysgeek
thursdaysgeek

Reputation: 7946

Oracle - both get XML date string and convert it to date

I am decoding an XML file (stored as a CLOB in the database) and there is a field that I want to pull out and store as a date.

If I select the field, I get data:

select xmlquery('declare namespace wx = "http://weather.obsfucate.com/rest-3.4/doc/"; //wx:validDateTime/text()' 
       PASSING XMLTYPE(wx.xml_result) 
       RETURNING CONTENT) as VALID_DATETIME
from OPSADM.WEATHER_XML wx
where DATATYPE = 'DailyObservation'

returns '2019-08-08T08:00:00+00:00'

If I try to insert that into a table with a DATE data type, it gives me an error: "ORA-00932: inconsistent datatypes: expected DATE got -"

So I try a conversion:

 select to_timestamp_tz ('2019-08-08T08:00:00+00:00', 'yyyy-mm-dd"T"hh24:mi:ssTZH:TZM')  OrigTime
 from dual

returns '8/8/2019 8:00:00.000000000 AM +00:0'

But if I try to put those two together, putting the to_timestamp_tz around my xmlquery

select to_timestamp_tz (xmlquery('declare namespace wx = "http://weather.obsfucate.com/rest-3.4/doc/"; //wx:validDateTime/text()' 
       PASSING XMLTYPE(wx.xml_result) 
       RETURNING CONTENT), 'yyyy-mm-dd"T"hh24:mi:ssTZH:TZM') as VALID_DATETIME
from OPSADM.WEATHER_XML wx
where DATATYPE = 'DailyObservation'

It returns an error: "ORA-00932: inconsistent datatypes: expected - got -"

So, it doesn't even know what it expects now.

This got me part of the way.

I'm pretty sure I'm missing something important that this shows., and this code gets me part of the way:

 select cast(to_timestamp_tz ('2019-08-08T08:00:00+00:00', 'yyyy-mm-dd"T"hh24:mi:ssTZH:TZM')
 at time zone 'US/Mountain' as date) as MountainTime
 , to_timestamp_tz ('2019-08-08T08:00:00+00:00', 'yyyy-mm-dd"T"hh24:mi:ssTZH:TZM')  OrigTime
 from dual

I think this shows my time zone:

 select sessiontimezone, dbtimezone from dual

returns -07:00 and +00:00, and I think the database is in either US Mountain or Central time.

I want to pull out the XML date, convert it to an appropriate date that can be inserted into a table. What am I doing wrong?

Upvotes: 1

Views: 837

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175726

You probably need to cast it:

select 
  to_timestamp_tz (CAST(xmlquery('declare namespace wx = "http://weather.obsfucate.com/rest-3.4/doc/"; //wx:validDateTime/text()' 
  PASSING XMLTYPE(wx.xml_result) 
  RETURNING CONTENT) AS VARCHAR2(100)), 'yyyy-mm-dd"T"hh24:mi:ssTZH:TZM') as VALID_DATETIME
from OPSADM.WEATHER_XML wx
where DATATYPE = 'DailyObservation'

Upvotes: 1

Related Questions