Comparing dates in Oracle using the decode function

That function will return date2 if date2 <= date1. Plugging in the values and translating to pseudo-code, you get if 0 - 0 = 0 then date2 else date1 where both dates are the same.


A better solution, if you're using 8i or later is to use case:

SELECT CASE WHEN date1 >= date2 THEN date2 ELSE date1 END FROM Your_Table;

Since case allows inequality operators, it's much more readable.


Or, if you want to be more succinct, you could use the function that's designed to return the lower of n values:

SELECT LEAST(date1, date2) FROM Your_Table;

(There is also a GREATEST function, which does the opposite.)


@Allan has already given you the best solution to me, but if you insist on using decode function, you can process the result of sign function instead.

http://www.techonthenet.com/oracle/functions/sign.php

sign(a) returns -1 if a < 0, 0 if a = 0 and 1 if a > 0. Thus, the following logic

if date1 >= date2 then
    return date1;
else
    return date2;
end if;

could be rewritten using decode in the following way:

select decode(sign(date2-date1), 
              -1 /*this means date 1 > date 2*/, date1 /* return date1*/, 
               0 /*dates are equal */,           date1 /* again, return date1*/,
               /*in any other case, which is date2 > date1, return date2*/ date2) 
from dual;