10 Replies Latest reply: Nov 28, 2013 9:37 AM by bencol RSS

    MONTHS_BETWEEN function returns 'wrong' negative values ???

    user13393428

      Hi,

       

      I can't figure out why these values are results.

       

      SQL> select months_between(to_date('02-FEB-13','DD-MON-RR'), to_date('28-FEB-13','DD-MON-RR')) from dual;

       

       

      MONTHS_BETWEEN(TO_DATE('02-FEB-13','DD-MON-RR'),TO_DATE('28-FEB-13','DD-MON-RR')

      --------------------------------------------------------------------------------

                                                                           -.83870968

       

       

       

      SQL> select months_between(to_date('02-FEB-13','DD-MON-RR'), to_date('01-APR-13','DD-MON-RR')) from dual;

       

       

      MONTHS_BETWEEN(TO_DATE('02-FEB-13','DD-MON-RR'),TO_DATE('01-APR-13','DD-MON-RR')

      --------------------------------------------------------------------------------

                                                                           -1.9677419

       

       

       

      SQL> select months_between(to_date('02-FEB-13','DD-MON-RR'), to_date('02-APR-13','DD-MON-RR')) from dual;

       

       

      MONTHS_BETWEEN(TO_DATE('02-FEB-13','DD-MON-RR'),TO_DATE('02-APR-13','DD-MON-RR')

      --------------------------------------------------------------------------------

                                                                                   -2

        • 1. Re: MONTHS_BETWEEN function returns 'wrong' negative values ???
          padders

          http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions089.htm

           

          If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative.

          • 2. Re: MONTHS_BETWEEN function returns 'wrong' negative values ???
            odie_63

             

            I can't figure out why these values are results.

             

            A quick lookup in the manual should help :

            http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions102.htm#SQLRF00669

            If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2.

            • 3. Re: MONTHS_BETWEEN function returns 'wrong' negative values ???
              bencol

              What values do you want?

              MONTHS_BETWEEN

              MONTHS_BETWEEN returns number of months between dates date1 and date2. The month and the last day of the

              month are defined by the parameter NLS_CALENDAR. If date1 is later than date2, then the result is positive. If

              date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days

              of the month or both last days of months, then the result is always an integer.

              Otherwise Oracle Database calculates the fractional portion of the result based

              on a 31-day month and considers the difference in time components date1 and date2.

               

              How do your results differ from this? E.g. (2-28)/31 = -0.83870968.

              • 4. Re: MONTHS_BETWEEN function returns 'wrong' negative values ???
                chris227

                What do you expect ?-)

                The difference of a day depends on the days of the specific month.

                Consider

                 

                select

                months_between(to_date('01-MAY-12','DD-MON-RR'), to_date('01-JUN-12','DD-MON-RR')) a

                ,months_between(to_date('01-APR-12','DD-MON-RR'), to_date('01-MAY-12','DD-MON-RR')) b

                ,months_between(to_date('01-APR-12','DD-MON-RR'), to_date('30-APR-12','DD-MON-RR')) c

                ,months_between(to_date('01-MAY-12','DD-MON-RR'), to_date('31-MAY-12','DD-MON-RR')) d

                ,1/31

                ,months_between(to_date('01-APR-12','DD-MON-RR'), to_date('30-APR-12','DD-MON-RR'))

                - 2/31 "c+"

                ,months_between(to_date('01-MAY-12','DD-MON-RR'), to_date('31-MAY-12','DD-MON-RR'))

                - 1/31 "d+"

                from dual;

                 

                ABCD1/31c+d+
                -1-1-.93548387096774193548387096774193548387-.96774193548387096774193548387096774194.032258064516129032258064516129032258065-1-1
                • 5. Re: MONTHS_BETWEEN function returns 'wrong' negative values ???
                  user13393428

                  But,

                   

                  select months_between(to_date('02-FEB-13','DD-MON-RR'), to_date('02-APR-13','DD-MON-RR')) from dual;


                  How it results -2? Shouldn't it be -1?

                  • 6. Re: MONTHS_BETWEEN function returns 'wrong' negative values ???
                    AlbertoFaenza

                    Hi,

                     

                    it could be worth also to mention a specific behavior of MONTHS_BETWEEN.

                     

                    In case the 2 dates are on the same day on different month, the time portion is not considered and result is always an integer.

                    When the 2 dates are on different days then also the time portion in considered and the result is changing.

                     

                    Here is an example:

                    alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

                     

                    select to_date('01-MAY-2013','dd-MON-yyyy') start_dt

                         , to_date('31-MAY-2013','dd-MON-yyyy')+(level-1)*6/24 end_dt

                         , months_between(to_date('01-MAY-2013','dd-MON-yyyy'), to_date('31-MAY-2013','dd-MON-yyyy')+(level-1)*6/24) diff

                      from dual

                    connect by level <= 10

                     

                    START_DT               END_DT                       DIFF

                    ---------------------- ---------------------- ----------

                    01-MAY-2013 00:00:00   31-MAY-2013 00:00:00   -0.9677419

                    01-MAY-2013 00:00:00   31-MAY-2013 06:00:00   -0.9758065

                    01-MAY-2013 00:00:00   31-MAY-2013 12:00:00   -0.9838710

                    01-MAY-2013 00:00:00   31-MAY-2013 18:00:00   -0.9919355

                    01-MAY-2013 00:00:00   01-JUN-2013 00:00:00           -1

                    01-MAY-2013 00:00:00   01-JUN-2013 06:00:00           -1

                    01-MAY-2013 00:00:00   01-JUN-2013 12:00:00           -1

                    01-MAY-2013 00:00:00   01-JUN-2013 18:00:00           -1

                    01-MAY-2013 00:00:00   02-JUN-2013 00:00:00   -1.0322581

                    01-MAY-2013 00:00:00   02-JUN-2013 06:00:00   -1.0403226

                     

                    Regards.

                    Alberto

                    • 7. Re: MONTHS_BETWEEN function returns 'wrong' negative values ???
                      chris227

                      Yeah, right, i knew it was intuitively :-)

                      • 8. Re: MONTHS_BETWEEN function returns 'wrong' negative values ???
                        BluShadow

                        user13393428 wrote:

                         

                        But,

                         

                        select months_between(to_date('02-FEB-13','DD-MON-RR'), to_date('02-APR-13','DD-MON-RR')) from dual;


                        How it results -2? Shouldn't it be -1?

                         

                          You really believe there is only 1 month between February and April?  have you got a calendar handy you can look at...

                        • 9. Re: MONTHS_BETWEEN function returns 'wrong' negative values ???
                          Mark T.

                          Here is another anomaly:

                           

                           

                          select months_between('29FEB2008','31JAN2008') from dual;

                          Answer returned by SQL: 1

                           

                           

                          I can agree with that.  From the last day of January to the last day of February is 1 month.  That's logical.

                           

                           

                          Now let's ADD a day to the time span.

                           

                           

                          select months_between('29FEB2008','30JAN2008') from dual;

                           

                           

                          Before we see the answer, let's imagine what the answer might be.  Might it be 1?  That doesn't feel right.  Or, because we're adding a day to the range, perhaps we will see 1.0322, which equals one month plus one day.

                           

                           

                          Answer returned by SQL: .9677419355

                           

                           

                          That's right, folks.  According to the awe-inspiring logic behind Oracle's months_between function, the number of months represented by the 31 days between 1/30 and 2/29 is actually LESS THAN the number of months represented by the 30 days between 1/31 and 2/29.

                           

                          Now, this could come in handy.  If you ever want to win a bar bet at OpenWorld, offer to prove that 31 is less than 30.

                           

                           

                          Heck of a function.

                          • 10. Re: MONTHS_BETWEEN function returns 'wrong' negative values ???
                            bencol

                            Works as documented:

                             

                            If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2

                             

                            > select 30/31 from dual;

                                 30/31
                            __________
                            .967741935