title: Querying data by time in Oracle SQL
body: <p>I have a table in Oracle in which data is inserted from different sources. In 2014, we discovered that some sources were not inserting the time stamp with the date, we however fixed them and now all sources insert data in proper format, example <code>05/04/2015 4:30:42 PM</code></p>
  
  <p>Using a SQL query, we want to divide the data into 3 types, morning shift, evening shift and no shift (for data without the time stamp).</p>
  
  <p>Morning shift will be between <code>00:00:00</code> and <code>14:30:00</code></p>
  
  <p>Evening shift will be between <code>14:31:00</code> and <code>23:59:00</code></p>
  
  <p>I have written the query in 2 different ways as follows:</p>
  
  <p>(a)</p>
  
  <pre><code> CASE
          WHEN TO_DATE(DATEANDTIME,'MM/DD/YYYY HH24:MI:SS') BETWEEN (TO_DATE ('00:00:00', 'HH24:MI:SS')) AND (TO_DATE ('14:30:00', 'HH24:MI:SS'))
          THEN
             'MORNING SHIFT'
          WHEN TO_DATE(DATEANDTIME,'MM/DD/YYYY HH24:MI:SS') BETWEEN (TO_DATE ('14:31:00', 'HH24:MI:SS')) AND  (TO_DATE ('23:59:00', 'HH24:MI:SS'))
          THEN
             'EVENING SHIFT'
          ELSE
            'NO SHIFT'
       END SHIFT
  </code></pre>
  
  <p>(b)</p>
  
  <pre><code>CASE
          WHEN DATEANDTIME BETWEEN (TO_DATE ('00:00:00', 'HH24:MI:SS')) AND (TO_DATE ('14:30:00', 'HH24:MI:SS'))
          THEN
             'MORNING SHIFT'
          WHEN DATEANDTIME BETWEEN (TO_DATE ('14:31:00', 'HH24:MI:SS')) AND  (TO_DATE ('23:59:00', 'HH24:MI:SS'))
          THEN
             'EVENING SHIFT'
          ELSE
            'NO SHIFT'
       END SHIFT
  </code></pre>
  
  <p>Query (a) returns an error <code>ORA-01858: a non-numeric character was found where a numeric was expected</code> but Query (b) displays data with everything going to <code>NO SHIFT</code></p>
  
  <p>Sample data of the table is as follows of the <code>dateandtime</code> field:</p>
  
  <pre><code>05/04/2015 10:43
  05/04/2015 16:52
  05/04/2015 19:27
  11/04/2009
  </code></pre>
  
  <p>Kindly guide me to the correct query and what am I doing wrong, the same conditions works in <code>TOAD</code> fine.</p>