To_char for Oracle SQL not working

Question

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

Did you mean the following: select * from INTERFACE_RUN where TO_DATE(INTERFACE_RUN.START_TIME, 'dd-mon-yy') = '04-MAY-10'; — StevieG
– StevieG, Commented Jun 13, 2016 at 14:21
And does SELECT TO_CHAR(INTERFACE_RUN.START_TIME, 'dd-mon-yy') FROM INTERFACE_RUN show the values you expect? — Álvaro González
– Álvaro González, Commented Jun 13, 2016 at 14:21
The query runs but does not fetch any data even after the data does exist — StevieG
– StevieG, Commented Jun 13, 2016 at 14:23
I beg your pardon? SELECT TO_CHAR(INTERFACE_RUN.START_TIME, 'dd-mon-yy') FROM INTERFACE_RUN does not return any row? Then your table is clearly empty, there's no way a WHERE clause can change that. Please double-check you're connecting to the correct server and database and typing the correct table name. — Álvaro González
– Álvaro González, Commented Jun 14, 2016 at 10:16

Gordon Linoff · Accepted Answer · 2016-06-13 14:20:55Z

6

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.

answered Jun 13, 2016 at 14:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

StevieG Over a year ago

Hey thanks a lot. The format of 'DD-MON-YY' did work.

Boneist Over a year ago

@Aniket why are you persisting in using 2-digit years? Years have got 4 digits; why introduce ambiguity when you don't have to?

StevieG Over a year ago

Because the data entries are present in that fashion. I cannot compare YYYY with YY

Gordon Linoff Over a year ago

@Aniket . . . Don't confuse the fact that Oracle prints out a date column as DD-MON-YY. In general, Oracle is keeping the full date.

Boneist Over a year ago

@Aniket To add to what Gordon said, if the column is of DATE datatype, then Oracle is storing information about the whole date, from the full year down to seconds. When it is displayed, it has to be converted to a string. You can explicitly set the format you want it to be displayed in (e.g.:

select to_char(sysdate, 'hh24:mi yyyy dd mm Day Month') from dual;) or you can rely on the nls_date_format parameter to automatically handle the conversion; this defaults to a format of DD-MON-RR. You can change that by doing

alter session set nls_date_format = '<format>';`.

|

Ricardo Arnold · Accepted Answer · 2016-06-13 19:29:19Z

4

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

edited Jun 13, 2016 at 19:29

answered Jun 13, 2016 at 14:21

Ricardo Arnold

9331 gold badge12 silver badges22 bronze badges

1 Comment

StevieG Over a year ago

Hey thanks a lot. The format of 'DD-MON-YY' did work.

Collectives™ on Stack Overflow

To_char for Oracle SQL not working

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related