Q:

To_char for Oracle SQL not working

i have the following query which does not retrieve data even though there is:
select *
from INTERFACE_RUN
where TO_CHAR(INTERFACE_RUN.START_TIME, 'dd-mon-yy') = '04-MAY-10';

The start_time field is a timestamp field. Hence I converted it using to_char and compared with the user passed value of 04-MAY-10. But it did not fetch any data.
Any help please. Thanks in advance

A:

to_char() pays attention to the case of the pattern.  So, you are producing `04-may-10', which is not the same.
So, try this:
where TO_CHAR(INTERFACE_RUN.START_TIME, 'DD-MON-YY') = '04-MAY-10';

That said, I much prefer:
where trunc(INTERFACE_RUN.START_TIME) = date '2010-05-04'

or:
where INTERFACE_RUN.START_TIME >= date '2010-05-04' AND
      INTERFACE_RUN.START_TIME < (date '2010-05-04') + 1

Why?  For two reasons.  First, the column is a date, so I prefer date comparisons.  Second, I prefer the ANSI/ISO standard date formats.  The second version can also readily take advantage of an index on the START_TIME column.

A:

Oracle will convert the date to lowercase, thus generating '04-may-10'
Try with 'DD-MON-YY' (uppercase)
select TO_CHAR(sysdate, 'dd-mon-yy') from dual
union all
select TO_CHAR(sysdate, 'dd-MON-yy') from dual;

TO_CHAR(S
---------
13-jun-16
13-JUN-16

