title: Compare timestamps in Oracle
body: <p>I have a column which is used to store date and time data in a VARCHAR2 column in my DB in Oracle 11g Express in the format of:</p>
  
  <p>9/30/2016 14:00:00</p>
  
  <p>I was trying out ways to get data between time ranges. I found the following 2 ways:</p>
  
  <pre><code>select * 
  from dummy
  WHERE starttime &gt; '9/30/2016 14:00:00' 
  AND starttime &lt; '9/30/2016 17:00:00' 
  order by starttime;
  
  select * 
  from dummy 
  WHERE to_timestamp(starttime, 'mm/dd/yyyy hh24:mi:ss') &gt; TO_TIMESTAMP('9/30/2016 14:00:00', 'mm/dd/yyyy hh24:mi:ss') 
  AND TO_TIMESTAMP(starttime, 'mm/dd/yyyy hh24:mi:ss') &lt; TO_TIMESTAMP('9/30/2016 17:00:00', 'mm/dd/yyyy hh24:mi:ss');
  </code></pre>
  
  <p>I was wondering how the first method works too as the column starttime is stored in VARCHAR format and without converting to a Timestamp the comparison still works. Could someone explain to me how/ why that happens or if there is some corner case for which it will not work? Thanks.</p>