SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

MONTHS_BETWEEN function returning wrong negative values

924842Mar 21 2012 — edited Apr 4 2012

Hello all,

The following is the query I am puzzling over:

select to_char(sysdate,'dd-MON-yyyy') AS TODAY, TO_CHAR(HYPOTHETICAL_DATE, 'dd-MON-yyyy') AS HYPOTHETICAL_DATE,
months_between(sysdate,HYPOTHETICAL_DATE) AS DIFF_IN_MONTHS from TableX

My result is:

TODAY HYPOTHETICAL_DATE DIFF_IN_MONTHS
21-MAR-2012 10-JUL-2020 -99,63109505675029868578255675029868578256

I am puzzled since in this case since the 2nd date is 8 years down the line my difference should be -8 and a fraction. So why am I getting -99?
Furthermore I would like to ignore the fractional part so the result I want for the above data is -8.

Please help.

Thanks

S. BASU

Locked Post

New comments cannot be posted to this locked post.

Locked on May 2 2012

Added on Mar 21 2012

3 comments

308 views