title: How to identify invalid (corrupted) values stored in Oracle DATE columns
body: <p>Oracle 10.2.0.5</p>
  
  <p>What is the easiest way to identify rows in a table that have "invalid" values in DATE columns. By "invalid" here what I mean is a binary representation that violates Oracle rules for date values.</p>
  
  <p>I recently had an issue with an invalid date stored in a column.</p>
  
  <p>I was able to use a query predicate to find a particular problematic row:</p>
  
  <pre><code>  WHERE TO_CHAR(date_expr,'YYYYMMDDHH24MISS') = '00000000000000'
  </code></pre>
  
  <p>In the case I had, the century byte was invalid...</p>
  
  <pre><code> select dump(h.bid_close_date) from mytable h where h.id = 54321
  
   Typ=12 Len=7: 220,111,11,2,1,1,1
  </code></pre>
  
  <p>The century byte should be 100 + two digit century. In this case, there was an extra 100 added, as if the century value was "120", making the year "12011".  (The only way I know to get invalid DATE values into the database is using OCI, using native 7-byte DATE representation.)</p>
  
  <p>In this case, the TO_CHAR function returned an identifiable string, which I could use for identifying the wonky DATE value.</p>
  
  <p>My question: is there an more general or easier approach (preferably using a SQL SELECT statement) to identify rows with "invalid" values in DATE columns.</p>