title: Date range problems in Oracle. How do I correctly grab date ranges in this format?
body: <p>I have this Oracle database, and it has a list of dates in this format: <code>mm/dd/yy 0:00:00 AM/PM</code> (1 for January, instead of 01; 1 for the day, instead of 01, and so forth).</p>
  
  <p>When I try a query such as:</p>
  
  <pre><code>select DATELIST from CORE.DATE_TEST
  where to_date(DATELIST) &gt;= to_date('8/27/2015', 'mm/dd/yy') AND to_date(DATELIST, 'mm/dd/yy') &lt;= to_date('9/27/2015', 'mm/dd/yy');
  </code></pre>
  
  <p>It gives me the following error: <code>ORA-01843: not a valid month</code>. I can't see how 27 is a valid month either, but it will accept that input if I try </p>
  
  <p>When I try to change it to the expected date formats: </p>
  
  <ol>
  <li><code>m/dd/yy</code></li>
  <li><code>mm/dd/yyyy</code></li>
  <li><code>m/d/yy</code></li>
  </ol>
  
  <p>I get this error: <code>ORA-01821: date format not recognized</code></p>
  
  <p>When I switch it up to <code>dd/mm/yy</code>, it sort of works, but not really; it gives me invalid results. </p>
  
  <p>However, this works:</p>
  
  <pre><code>select DATELIST from CORE.DATE_TEST
  where to_date(DATELIST) &gt;= to_date('8/27/2015', 'mm/dd/yy') 
  </code></pre>
  
  <p>The PL/SQL script is breaking on the following addition: </p>
  
  <pre><code>AND to_date(DATELIST, 'mm/dd/yy') &lt;= to_date('9/27/2015', 'mm/dd/yy');
  </code></pre>
  
  <p>When I replace the above code with this:</p>
  
  <pre><code>SELECT to_date(DATELIST, 'dd/mm/yy') 
      FROM CORE.DATE_TEST
  WHERE DATELIST &gt;= to_date('8/27/2015', 'mm/dd/yy')
      AND DATELIST &lt;= to_date('9/27/2015', 'mm/dd/yy');
  </code></pre>
  
  <p>It works, and is a million times faster, but it won't correctly parse the date range. The lesser-than-or-equal-to (<code>&lt;=</code>) operator is only grabbing data between <code>8/27/2015</code>, and <code>9/26/2015</code>. Isn't it supposed to get 8/27/2015 to 9/27/2015?</p>
  
  <p>So I change the last line to this:</p>
  
  <pre><code>AND DATELIST &lt;= to_date('9/27/2015', 'mm/dd/yy') + 1
  </code></pre>
  
  <p>...and it works, but it doesn't seem like I'm handling this right.</p>
  
  <p>What am I doing wrong? Also, if there's a better, more efficient way of doing this, I'm all ears.</p>