Oracle to_char in query is not working

Why don't you use TO_DATE() on the date instead?

select * from RP_REPORT_TEMP 
where START_DATE >= to_date('May 01 2016 12:00:00 AM','MON DD YYYY HH:MI:SS AM')
  and END_DATE <= to_date('May 31 2016 11:59:00 AM','MON DD YYYY HH24:MI:SS AM')
  and lower(rid) like '%a001%'

Assuming that START_DATE and END_DATE are actually dates, I can spot two main issues in your code:

1. Casting every row to string is slow and also prevents Oracle from using indexes (unless you have a carefully built function-based index).

2. Lexicographical sort (aka "A to Z") does not render meaningful results with dates with "May 1". Just think of what sense this list has: Apr, Aug, Dec, Feb, Jan, Jul, Jun, Mar, May, Nov, Oct, Sep

My suggestions are:

• Avoid converting dates to string, save for display purposes
• Use a more solid format when you have to convert from string to date

As a result:

select * 
  from RP_REPORT_TEMP 
 where START_DATE
          >= TO_DATE('2016-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
   and END_DATE
          <= TO_DATE('2016-05-31 11:59:00', 'YYYY-MM-DD HH24:MI:SS')
   and lower(rid) like '%a001%' 
 order by CAST(cid as INTEGER) asc

P.S. START_DATE and END_DATE were not dates after all

Try this..

 select * 
      from RP_REPORT_TEMP 
     where TO_date(START_DATE,'MON DD YYYY HH:MI:SS AM') 
              >= to_Date('May 01 2016 12:00:00 AM' ,'MON DD YYYY HH:MI:SS AM') 
       and TO_DATE(END_DATE,'MON DD YYYY HH:MI:SS PM') 
              <= TO_DATE('May 31 2016 11:59:00 AM' ,'MON DD YYYY HH:MI:SS PM') 
       and lower(rid) like '%a001%' 
     order by CAST(cid as INTEGER) asc

select * 
      from RP_REPORT_TEMP 
     where TO_date(START_DATE,'MON DD YYYY HH24:MI:SS') 
              >= to_Date('May 01 2016 12:00:00' ,'MON DD YYYY HH24:MI:SS') 
       and TO_DATE(END_DATE,'MON DD YYYY HH24:MI:SS') 
              <= TO_DATE('May 31 2016 11:59:00' ,'MON DD YYYY HH24:MI:SS') 
       and lower(rid) like '%a001%' 
     order by CAST(cid as INTEGER) asc

This is what your query should look like:

select   * 
from     rp_report_temp 
where    to_date(start_date,'FM Mon DD YYYY HH:MI:SS"FMMon DD YYYY HH:MI:SS":000"AM', 'nls_date_language = english') 
            >= to_date('01/05/2016 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
and      to_date(end_date,'FM Mon DD YYYY HH:MI:SS":000"AM', 'nls_date_language = english') 
            <= to_date('31/05/2016 23:59:59', 'dd/mm/yyyy hh24:mi:ss')
and      lower(rid) like '%a001%' 
order by cast(cid as integer) asc;

Here, I have converted your start and end date columns into a date, based on the fact that a sample string in that column looks like "May 1 2016 12:00:00:000AM" - there's an extra ":000" after the seconds; I'm assuming this is always ":000".

Note how I've made your code nls_date_language parameter independent, by specifically setting that value within the to_date(). This means that if someone else's client has that set to something that's not English, the sql statement will still work for them.

You'll note that I didn't need to do that for the values you're testing, since I changed the format of the string to something that used numbers to represent the months.

You have some fundamental data modelling issues here too - by storing everything as varchar2 you have made a rod for your own back - you've lost all the validation available (what happens if someone puts a string of Feb 30 2015 39:99:99 AM in that column? AWOOGA, AWOOGA, error!). You've made your queries have to work harder (now we've got functions all over the place trying to convert data to the correct datatype).

You should store data in the correct datatype. Dates should be stored as DATE, Timestamps as TIMESTAMP, numbers as NUMBER (with or without specific precision and scale), etc etc.

If your data was stored correctly, then your query would be:

select   *
from     rp_report_temp
where    start_date >= to_date('01/05/2016 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
and      end_date <= to_date('31/05/2016 23:59:59', 'dd/mm/yyyy hh24:mi:ss')
and      lower(rid) like '%a001%'
order by cid asc;