Display dates not in ascending for To_Char can i know why and how i can solve the problem whereby if i use To_Char it does not sort the date in ascending. Below is my code

    select distinct To_Char (attendance_Date, 'dd/mm/yyyy')
    from DIT_2010MAR_CIT4114A_FYP1_NO
    order by 1 asc


result obtained is



DATE     
----------
01/05/2016
02/05/2016
03/04/2016
04/04/2016
05/04/2016
06/04/2016
If you sort by dates formatted in DD/MM/YYYY, then the sorting will put all the first of month first (of every month), then the seconds of month, etc.

You can't sort by a column not included in a select distinct, though. To sort by ascending date, group by and use the actual date column instead of the formatted date via to_char:

select To_Char (attendance_Date, 'dd/mm/yyyy')
from DIT_2010MAR_CIT4114A_FYP1_NO
group by attendance_Date
order by attendance_Date asc


If attendance_Date is a timestamp, cast it to date first (everywhere in the query).