CODEWITHSUNDEEP
sqloracle-database
StevieG
StevieG

Reputation: 309

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

Upvotes: 1

Views: 8350

Answers (2)

Ricardo Arnold
Ricardo Arnold

Reputation: 913

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

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269663

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.

Upvotes: 6

Related Questions