Skip to main content

Avoid TO_CHAR in an ORDER BY clause

The usage of TO_CHAR function in a ORDER BY is almost always a mistake. The column value will be ordered alphabetically instead of being ordered according its datatype.

Noncompliant Code Example

Suppose we have the data below in the EMP table:

empnohiredate
12019-10-01
52019-10-10
152018-10-02
202018-10-20

The queries below return an unwanted result:

select empno from emp order by to_char(empno);

empno
1
15
20
5

select hiredate from emp order by to_char(hiredate, 'dd-mm-rrrr');

hiredate
01-OCT-19
02-OCT-18
10-OCT-19
20-OCT-18

Compliant Solution

To order these columns correctly we must remove the TO_CHAR call.

select empno from emp order by empno;

empno
1
5
15
20

select hiredate from emp order by hiredate;

hiredate
02-OCT-18
20-OCT-18
01-OCT-19
10-OCT-19