how to do order by on a to_char column

Question

I have a following oracle query:

   SELECT a.USER_ID,
           c.first_name,
           c.last_name,
           TO_CHAR( b.logon_date, 'MM/DD/YYYY HH:MI:SS am') logon_date,
           NVL(TO_CHAR( b.logoff_date, 'MM/DD/YYYY HH:MI:SS am'), '') logoff_date,
           a.session_id
      FROM table a,
           table b,
           table c
       WHERE a.row_id >= start_row
               AND a.row_id <= end_row
               AND a.session_id = b.session_id
               AND a.USER_ID = b.USER_ID
               AND a.USER_ID = RTRIM(LTRIM(c.USER_ID))
      ORDER BY logoff_date DESC

query works fine if I remove the order by

I want to order the results of this query in DESC order based on logoff_date on which I am using to_char to make it a date with time in am/pm

I tried giving following as order_by

NVL(TO_CHAR( b.logoff_date, 'MM/DD/YYYY HH:MI:SS am'), '')

but results come back as:

03/03/2010 12:59:37 am
03/03/2010 12:53:12 pm
03/03/2010 12:41:40 pm
03/03/2010 12:19:38 am
03/03/2010 11:34:04 am
03/03/2010 10:41:47 am
03/03/2010 10:16:16 pm
03/03/2010 10:14:45 pm
03/03/2010 09:59:54 am
03/03/2010 07:36:17 pm

Answer 1

Hi drake,

Currently you are ordering by the character value of your date because logoff_date references the alias logoff_date in the select (NVL(TO_CHAR( ...))). This character value will usually be ordered alphabetically and will not match the ordering of the DATE column.

If you want to order by the DATE value of the column, you will have to reference it precisely:

ORDER BY b.logoff_date
         ^^