Comparing dates in Oracle using the decoding function

Question

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

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

What are the states (bottom) that the lower decoding function will return date2 if date1> date2:

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

Will date2 return if date1> = date2?

Or is it simple if date1> date2?

Is there an easier solution?

Answer 1

This function will return date2 if date2 <= date1. By connecting the values ​​and translating into pseudo-code, you will get if 0 - 0 = 0 then date2 else date1 , where both dates are the same.

The best solution if you are using 8i or newer is to use case :

 select case when date1 >= date2 then date2 else date1 end from Your_Table; 

Since case allows inequality operators, this is much readable.

Answer 2

@Allan already gave you the best solution for me, but if you insist on using the decode function, you can process the result of the sign function.

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

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

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

can be rewritten with decode as follows:

 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; 

Answer 3

You can try months_between . 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 parameter is larger than the second, so it will return 1. The second line returns ~ 0.48 (when executed at 11:30 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; 

Generally:

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

Update:

After some experimentation, it seems that the best graininess 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 methods here: http://www.orafaq.com/faq/how_does_one_get_the_time_difference_between_two_date_columns

Answer 4

If you are trying to check by date - that is, each time 1/1 is less than 1/2, and each 1/1 is equal to each other time 1/1, even if Oracle DATE is longer - then you want to compare as follows:

TRUNC (DATE1) <= TRUNC (DATE2)

I do not see this in other answers, it is so fundamental that I wonder if I do not understand this question.

Answer 5

will return date2 when date1> = date2

Answer 6

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