Re: Months_between

Hi John,

This is not exactly odd, but yes for some applications, it might create problems.

For your example.

Year 2000 is a leap year and Year 2001 is NOT.... that should explain the results you are getting.
(Explaination is given below)

For months_between :

1. Last date of X year minus Last date of Y year gives you the round figure in months.
2. Last date of X year minus same date of Y year gives you the round figure in months. Again in Case of 1 and 2 if year in both the cases are some.. you will get the same results.
3. Last date of X year and any other date (Minus Case 1 and 2) will give you a fraction.

       This is true no matter what, you take LEAP YEAR or Otherwise.

Hope this solves your querry..

Need more examples or if the explanation is not clear.. let me know.. Feel free to correct me.

Thanks

Nikunj

• Original Message ----- From: "John Phan" <johnphanz_at_hotmail.com> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Monday, October 16, 2000 12:38 AM Subject: Months_between

> Hi,
> I've got some problems with my application. I have used "months_between"
> function of Oracle to calculate number of month between two days, but I
> really do not understand why the result is so strange.
> Could you explain to me what happend as follows:
>
> SQL> select months_between('28/02/2001','28/11/2000') from dual;
>
> MONTHS_BETWEEN('28/02/2001','28/11/2000')
> -----------------------------------------
> 3
>
> SQL> select months_between('28/02/2001','29/11/2000') from dual;
>
> MONTHS_BETWEEN('28/02/2001','29/11/2000')
> -----------------------------------------
> 2.9677419
>
> SQL>select months_between('28/02/2001','30/11/2000') from dual;
>
> MONTHS_BETWEEN('28/02/2001','30/11/2000')
> -----------------------------------------
> 3
>
> SQL>select months_between('28/02/2000','28/11/1999') from dual;
>
> MONTHS_BETWEEN('28/02/2000','28/11/1999')
> -----------------------------------------
> 3
>
> SQL>select months_between('28/02/2000','29/11/1999') from dual;
>
> MONTHS_BETWEEN('28/02/2000','29/11/1999')
> -----------------------------------------
> 2.9677419
>
> SQL>select months_between('28/02/2000','30/11/1999') from dual;
>
> MONTHS_BETWEEN('28/02/2000','30/11/1999')
> -----------------------------------------
> 2.9354839
>
> Thanks for your help,
>
> John.
> _________________________________________________________________________
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
>
> Share information about yourself, create your own public profile at
> http://profiles.msn.com.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: John Phan
> INET: johnphanz_at_hotmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Mon Oct 16 2000 - 02:55:14 CDT