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:
| empno | hiredate |
|---|---|
| 1 | 2019-10-01 |
| 5 | 2019-10-10 |
| 15 | 2018-10-02 |
| 20 | 2018-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 |