Comparing dates in Oracle using the decode function

Question

I need to compare two dates using the Oracle decode function to see if one is less than or equal to the other.

I found this article - http://www.techonthenet.com/oracle/functions/decode.php

Which states (at the bottom) that the below decode function will return date2 if date1 > date2 :

decode((date1 - date2) - abs(date1 - date2), 0, date2, date1)

Would this not return date2 if date1 >= date2 ?

Or is it just if date1 > date2?

Is there an easier solution?

Foil

Answer 1

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.)

Bookseller

Answer 2

@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;

Passade

Answer 3

You could try the months_between function. It will calculate the number of months between two dates, as a decimal number.

select months_between(sysdate+30, sysdate ) from dual;
select months_between(sysdate+15, sysdate ) from dual;

In this example, the first paramater is greater than the second so it will return 1. The second line returns ~0.48 (when executed at about 11:30 AM on 2010-09-01) To get the actual date values:

select case when months_between(sysdate+30, sysdate ) > 0 then sysdate+30 else sysdate end from dual;

In general:

case when months_between(dateA, dateB ) > 0 then dateA else dateB

Update:

After some experimentation, it seems the finest granularity of this function is Day.

select months_between(to_date('2010-10-16 23:59:59', 'YYYY-MM-DD HH24:MI:SS'),
                       to_date('2010-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
from dual;

...will return 0

but

select months_between(to_date('2010-10-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),
                       to_date('2010-10-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
from dual;

will return 0.032258064516129.

Some other interesting date difference/compare techniques here: http://www.orafaq.com/faq/how_does_one_get_the_time_difference_between_two_date_columns

Bathhouse

Answer 4

If you're trying to check by date - that is, every time in 1/1 is less than 1/2, and every on 1/1 is equal to every other time on 1/1, even if the Oracle DATE is greater - then you want to compare as follows:

TRUNC(DATE1) <= TRUNC(DATE2)

I don't see this in the other answers, it is so basic it makes me wonder if I'm misunderstanding the question.

Monolatry

I see now that you explicitly said you wanted DECODE. I would use CASE for a question like this where you're evaluating a boolean condition. Regardless, don't forget to cut off the minutes when you're comparing dates, unless you want the comparison to the maximum precision. – Monolatry

Excellent point, I accounted for that already as I work with dates quite often but it's a good one to mention if others end up looking at this. – Foil

Answer 5

This is better:

decode(sign(trunc(sysdate) - (trunc(sysdate))), 1, 1, -1, -1, 0 , 0)

1: date 1 > date 2
0: date 1 = date 2
-1: date 1 < date 2

Lunation

Answer 6

will return date2 when date1 >= date2

Atchison