Order by the numeric value of days using to_char


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                      HIREDATE DATE);

INSERT INTO EMP VALUES (1, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (2, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (3, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (4, TO_DATE('2-APR-1981',  'DD-MON-YYYY'));
INSERT INTO EMP VALUES (5, TO_DATE('28-SEP-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (6, TO_DATE('1-MAY-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (7, TO_DATE('9-JUN-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (8, TO_DATE('09-DEC-1982', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (9, TO_DATE('17-NOV-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (10,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (11,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (12,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (13,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'));
INSERT INTO EMP VALUES (14,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'));


SQL> select empno, to_char(hiredate,'fmD') "Day" from emp order by "Day";

     EMPNO D
---------- -
         3 1
         5 2
         7 3
        10 3
         9 3
        11 4
         1 4
        12 5
         8 5
        13 5
         4 5
         6 6
         2 6
        14 7

14 rows selected.

SQL>
Home »
Oracle »
Conversion Functions » 

to_char:
  1. to_char function
  2. Format salary with to_char
  3. Order by the numeric value of days using to_char
  4. to_char with time format
Related: