Dynamic order by date data type in Oracle using CASE

Question

My code in the stored procedure:

SELECT * FROM
my_table ir
WHERE 
--where clause goes here
ORDER BY   
       CASE  WHEN p_order_by_field='Id' AND p_sort_order='ASC'  THEN IR.ID end,
       CASE  WHEN p_order_by_field='Id' AND p_sort_order='DESC'  THEN IR.ID end DESC,
       CASE  WHEN p_order_by_field='Date' AND p_sort_order='ASC'  THEN TO_CHAR(IR.IDATE, 'MM/dd/yyyy') end,
       CASE  WHEN p_order_by_field='Date' AND p_sort_order='DESC'  THEN TO_CHAR(IR.IDATE, 'MM/dd/yyyy') end DESC;

The problem is that sorting is done based on the char, which comes out wrong for the date case. CASE statement, however, won't allow any other datatype other than char. So what is the solution in this case? I need to be able to pass the p_order_by_field into the stored procedure.

Answer 1

Should be simple - just use ISO date format in your case:

TO_CHAR(IR.IDATE, 'yyyy-mm-dd')

and you should be fine.