title: Oracle - both get XML date string and convert it to date
body: <p>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.</p>
  
  <p>If I select the field, I get data:</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>returns '2019-08-08T08:00:00+00:00'</p>
  
  <p>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 -"</p>
  
  <p>So I try a conversion:</p>
  
  <pre><code> select to_timestamp_tz ('2019-08-08T08:00:00+00:00', 'yyyy-mm-dd"T"hh24:mi:ssTZH:TZM')  OrigTime
   from dual
  </code></pre>
  
  <p>returns '8/8/2019 8:00:00.000000000 AM +00:0'</p>
  
  <p>But if I try to put those two together, putting the to_timestamp_tz around my xmlquery</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>It returns an error: "ORA-00932: inconsistent datatypes: expected - got -"</p>
  
  <p>So, it doesn't even know what it expects now.</p>
  
  <p><a href="https://stackoverflow.com/questions/19392840/convert-oracle-string-to-date-wtih-timezone">This got me part of the way.</a></p>
  
  <p><a href="https://stackoverflow.com/questions/41228576/why-does-oracles-to-timestamp-tz-return-the-wrong-time-and-or-time-zone">I'm pretty sure I'm missing something important that this shows.</a>, and this code gets me part of the way:</p>
  
  <pre><code> 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
  </code></pre>
  
  <p>I think this shows my time zone:</p>
  
  <pre><code> select sessiontimezone, dbtimezone from dual
  </code></pre>
  
  <p>returns -07:00 and +00:00, and I think the database is in either US Mountain or Central time.</p>
  
  <p>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?</p>