title: Oracle SQL - Selecting a date from an XML tag and casting it to a DATE type to allow comparison of the dates
body: <p>I have a CLOB type column (called xml) that stores an xml document. The xml document has a tag called <code>&lt;productionDate&gt;</code>. </p>
  
  <p>What i want to do is search the table and retrieve any row that contains an xml document that has productionDate between two dates. </p>
  
  <p>I know how to read the xml tag as a value:</p>
  
  <pre><code>Select 
  xmltype(xml).extract('//product/productionDate/text()').getStringVal() from myTable
  </code></pre>
  
  <p>The above query returns the following date. </p>
  
  <pre><code>1999-09-23 00:00:00.0 UTC
  2000-01-18 00:00:00.0 UTC
  2000-01-18 00:00:00.0 UTC
  1999-11-02 00:00:00.0 UTC
  1999-11-02 00:00:00.0 UTC
  1999-11-02 00:00:00.0 UTC
  1999-11-02 00:00:00.0 UTC
  1999-11-02 00:00:00.0 UTC
  1999-11-02 00:00:00.0 UTC
  </code></pre>
  
  <p>I want to only list the ones that are between 01-NOV-1999 and and 01-JAN-2000. To do this i tried to cast the value to a DATE type</p>
  
  <pre><code>Select 
  XMLCast(xmltype(xml).extract('//product/productionDate/text()').getStringVal() as DATE) from myTable
  </code></pre>
  
  <p>I get the following error:</p>
  
  <pre><code>ORA-01830: date format picture ends before converting entire input string
  </code></pre>
  
  <p>Presumably this is because the format of the date cannot be converted to a date format. What kind i do to read the date as a valid date (Ignoring the time and the text UTC) so that i can do a comparison on the date. </p>
  
  <p>Maybe a substring might work but i want to hear what other more efficient options are available out there. </p>