Can someone let me know how the date syntax work. example working with "greatest" 01-oct-02 and 20-may-2003, it accept 20-may-2003 to be the greatest instead of 01-oct-02 which the oldest (greatest) can someone explain this to me. please let me know the logic behind. I have alot of problems with this.

You Have to use Date Fromat To_Date
('20-may-2003','dd-mon-yyyy') and to_date('01-oct-2002','dd-mon-yyyy')
and you find between this greatest.

thank you for your support, but i tried the syntax you sent to me, it will only show the appearance of the date dd mm yy.

when oracle tries to compare the two date to see the one that is oldest, because one have to come before the other everyone it is known that '01-oct-2002' will come first before '20-may-2003' just a mere look. But oracle does not work like that, it works differently try comparing the dates using the greatest function. you see what i am talking about. i want to know the logic.

why can't you use 'least' function to get the oldest(Greatest) date.....
your requirement is to get Greatest(Oldest) date right?,So you can use "least" function instead of "greatest" function..........
I think it will help you ..........................