7 Replies Latest reply: Jan 17, 2006 3:34 PM by 483246 RSS

    PL/SQL:  INTERVAL YEAR TO MONTH

    483246
      I am utilizing INTERVALs to determine the number of years and the number of months between two dates. Using the example dates below, I was expecting the EXTRACT(YEAR FROM A) to return 4 years and the EXTRACT(MONTH FROM A) to return 11 months (at this point, the number of days is not important). However, 5 years and 0 months is being EXTRACTed. I don't understand why 4 years and 11 months is not being EXTRACTed instead...

      Any help would be very appreciated. Thanks!

      Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit
      SQL*Plus: Release 9.2.0.1.0

      SET SERVEROUTPUT ON;
      DECLARE
      start_date DATE;
      end_date DATE;
      A INTERVAL YEAR TO MONTH;
      B INTERVAL DAY (4) TO SECOND (9);
      BEGIN

      start_date := TO_DATE('07/01/1996','MM/DD/YYYY');
      end_date := TO_DATE('06/20/2001','MM/DD/YYYY');

      A :=( end_date - start_date ) YEAR TO MONTH;
      DBMS_OUTPUT.PUT_LINE( A );
      DBMS_OUTPUT.PUT_LINE( EXTRACT(YEAR FROM A)||' Years and '||
      EXTRACT(MONTH FROM A)||' Months' );

      B :=( end_date - start_date ) DAY TO SECOND;
      DBMS_OUTPUT.PUT_LINE( EXTRACT(DAY FROM B)||' Days');

      A :=( TO_DATE('06/20/2001','MM/DD/YYYY') - TO_DATE('07/01/2000','MM/DD/YYYY') ) YEAR TO MONTH;
      DBMS_OUTPUT.PUT_LINE( EXTRACT(YEAR FROM A)||' Years and '|| EXTRACT(MONTH FROM A)||' Months' );

      END;

      The output is

      +05-00
      5 Years and 0 Months
      1815 Days
      1 Years and 0 Months

      PL/SQL procedure successfully completed.
        • 1. Re: PL/SQL:  INTERVAL YEAR TO MONTH
          Dmytro Dekhtyaryuk
          Hallo,
          interesting question, it seemed that function year to month performs implicit
          round to nearest value:

          Compare:
          DECLARE
          start_date DATE;
          end_date DATE;
          A INTERVAL YEAR TO MONTH;
          B INTERVAL DAY (4) TO SECOND (9);
          BEGIN

          start_date := TO_DATE('07/01/1996','MM/DD/YYYY');
          end_date := TO_DATE('06/20/2001','MM/DD/YYYY');

          A :=( end_date - start_date ) YEAR TO MONTH;
          DBMS_OUTPUT.PUT_LINE( A );
          DBMS_OUTPUT.PUT_LINE( EXTRACT(YEAR FROM A)||' Years and '||
          EXTRACT(MONTH FROM A)||' Months' );

          B :=( end_date - start_date ) DAY TO SECOND;
          DBMS_OUTPUT.PUT_LINE( EXTRACT(DAY FROM B)||' Days');

          A :=( TO_DATE('06/20/2001','MM/DD/YYYY') - TO_DATE('07/01/2000','MM/DD/YYYY') ) YEAR TO MONTH;
          DBMS_OUTPUT.PUT_LINE( EXTRACT(YEAR FROM A)||' Years and '|| EXTRACT(MONTH FROM A)||' Months' );

          END;

          The output is

          +05-00
          5 Years and 0 Months

          1815 Days
          1 Years and 0 Months

          DECLARE
          start_date DATE;
          end_date DATE;
          A INTERVAL YEAR TO MONTH;
          B INTERVAL DAY (4) TO SECOND (9);
          BEGIN

          start_date := TO_DATE('07/01/1996','MM/DD/YYYY');
          end_date := TO_DATE('06/02/2001','MM/DD/YYYY');

          A :=( end_date - start_date ) YEAR TO MONTH;
          DBMS_OUTPUT.PUT_LINE( A );
          DBMS_OUTPUT.PUT_LINE( EXTRACT(YEAR FROM A)||' Years and '||
          EXTRACT(MONTH FROM A)||' Months' );

          B :=( end_date - start_date ) DAY TO SECOND;
          DBMS_OUTPUT.PUT_LINE( EXTRACT(DAY FROM B)||' Days');

          A :=( TO_DATE('06/20/2001','MM/DD/YYYY') - TO_DATE('07/01/2000','MM/DD/YYYY') ) YEAR TO MONTH;
          DBMS_OUTPUT.PUT_LINE( EXTRACT(YEAR FROM A)||' Years and '|| EXTRACT(MONTH FROM A)||' Months' );

          END;

          The output is

          +04-11
          4 Years and 11 Months

          1815 Days
          1 Years and 0 Months

          If end_date = TO_DATE('06/15/2001','MM/DD/YYYY')
          we get 4 Years 11 Months,

          if end_date = TO_DATE('06/16/2001','MM/DD/YYYY')
          we get already 5 years 0 Months

          HTH
          Regards
          Dmytro
          • 2. Re: PL/SQL:  INTERVAL YEAR TO MONTH
            Solomon Yakobson
            Well, INTERVAL YEAR TO MONTH does not store fractional portion of the month. So when you assign it a value that is not a whole number of months, roundup logic is used. Same as in NUMBER assignment operator:

            SQL> set serveroutput on
            SQL> declare
            2 x number(5);
            3 begin
            4 x := 4.1;
            5 dbms_output.put_line(x);
            6 x := 4.5;
            7 dbms_output.put_line(x);
            8 end;
            9 /
            4
            5

            PL/SQL procedure successfully completed.

            SQL>

            In your case the difference in months is:

            SQL> set serveroutput on
            SQL> declare
            2 start_date date;
            3 end_date date;
            4 begin
            5 start_date := TO_DATE('07/02/1996','MM/DD/YYYY');
            6 end_date := TO_DATE('06/17/2001','MM/DD/YYYY');
            7 dbms_output.put_line(months_between(end_date,start_date));
            8 end;
            9 /
            59.48387096774193548387096774193548387097

            PL/SQL procedure successfully completed.

            SQL>

            which rounds up to 60 moths or 5 years and 0 months. It appears you want just whole number of months. Therefore you could use something like:

            SQL> SET SERVEROUTPUT ON;
            SQL> DECLARE
            2 start_date DATE;
            3 end_date DATE;
            4 A INTERVAL YEAR TO MONTH;
            5 m NUMBER;
            6 BEGIN
            7 start_date := TO_DATE('07/01/1996','MM/DD/YYYY');
            8 end_date := TO_DATE('06/20/2001','MM/DD/YYYY');
            9 m := MONTHS_BETWEEN(end_date,start_date);
            10 IF m = TRUNC(m)
            11 THEN
            12 A := (end_date - start_date) YEAR TO MONTH;
            13 else
            14 A := (end_date - start_date) YEAR TO MONTH - TO_YMINTERVAL('0-1');
            15 END IF;
            16 DBMS_OUTPUT.PUT_LINE(A);
            17 END;
            18 /
            +04-11

            PL/SQL procedure successfully completed.

            SQL>

            SY.
            • 3. Re: PL/SQL:  INTERVAL YEAR TO MONTH
              483246
              I see how rounding is having the effect on the year-month output. However, wouldn't this be a "bug" with the INTERVAL YEAR TO MONTH functionality. It appears to round-up the number of months (and the year when number of months is 12) when the number of days remaining for the month is > 1/2 of the month. This is inconsistent since the number of months is expected regardless of how many days occur:

              start date end date INTERVAL NUMBER
              07/01/1996 06/02/2001 +04-11 4.9232...
              07/01/1996 06/15/2001 +04-11 4.9589...
              07/01/1996 06/16/2001 +05-00 4.9616...
              07/01/1996 06/20/2001 +05-00 4.9726...

              I need the number of years and the number of months between two dates for the application that I am developing. I, originally, used this calculation: (end date minus start date) / 365 }; if there was no remainder, only years occurred between the two dates, otherwise, years and months existed between the two dates; However, this would not work when one or more leap years occurred between the two dates. I wanted to avoid looping through all of the years between the two days and determining if it was leap year and subtracting the total leap year count from the days difference before dividing by 365.

              The INTERVAL YEAR TO MONTH worked for a large set of test cases... and then I just encountered this one.

              Incorporating the MONTHS_BETWEEN may work for some scenarios, but b/c it assumes that each month is 31 days, subtracting one month from the INTERVAL YEAR TO MONTH may not be correct for other scenarios.

              Can this be reported as a bug so that INTERVAL YEAR TO MONTH does not round-up the months (and possibly the year when 12 months reached) when the number of days is > 1/2 the month? The interval between 07/01/1996 and 06/20/2001 in years-months is 4 years and 11 months.
              • 4. Re: PL/SQL:  INTERVAL YEAR TO MONTH
                dainge
                A suggestion,
                For the calculation, format your dates to always be the 1st of the month, then you have consistent rounding.

                start_date := to_date('01'||to_char(input_date,'MMYYYY'),'DDMMYYYY');

                Or the last day for that matter:
                start_date := last_day(input_date);
                • 5. Re: PL/SQL:  INTERVAL YEAR TO MONTH
                  Laurent Schneider
                  since a "month" is not a mathematical unit (it is a variable number of days), there is no mathematically correct definition of the number of months between 2 dates.

                  I have always argued that there is more than 1 year between 28-02-2003 and 29-02-2004 and less than one year between 29-02-2004 and 28-02-2005.

                  You can have a look at my years_between and add_years functions at http://laurentschneider.blogspot.com/2005/12/addyears-and-yearsbetween_01.html but be prepared, they are not "human readable"...

                  To define what you expect, you must specify your business rules. If you want to have the "month_between" logic, than use "month_between". Otherwise, be aware to know each month can have a different number of day! Only once you have your specification written, we can start with giving solutions...

                  Message was edited by:
                  Laurent Schneider
                  29 not 28
                  • 6. Re: PL/SQL:  INTERVAL YEAR TO MONTH
                    dainge
                    Laurent, Hello again :)

                    I'm beginning to agree with you that there's no single universal answer to questions like these. I've always considered a number of weeks or months as integers and that has worked out fine the few times I had to deal with the issue.
                    But, you're right. That cannot be the answer to every such question.

                    In this particular case, I got the impression that OP was happy with the result as produced by dates in the first half of the month.

                    Mike
                    • 7. Re: PL/SQL:  INTERVAL YEAR TO MONTH
                      483246
                      The business rules in this case want to "ignore" the leap year day, 02-29, for calculation purposes. Annual base amounts are based on 365 days, not 366 days, so the "business" years that are applicable/calculated must be based on 365 days to retrieve the respective annual base amounts.

                      The number of years and number of months is required to determine which annual base amount(s) to retrieve... if the annual base amount is based on a single "business" year or if the annual base amount is split between two "business" years.

                      The business rules do not allow 02-29 to be entered as a start or end date; starting 02-28-2003 and ending 02-29-2004 would have to be be ending 02-28-2004 (which would be = 1 yr.) or ending 03-01-2004 (which would be > 1 yr.)

                      I am going to look into using dainge's suggestion by forcing the ending date to be set to the 1st day of the month to calculate the INTERVAL YEAR TO MONTH in order to be consistent and avoid rounding for any ending dates > the 15th.

                      I appreciate everyone's help. If you have any other suggestions, please let me know...

                      I still think that the INTERVAL YEAR TO MONTH rounding behavior ramifications are misleading since dates for the 1st through 15th of the month do not round-up the INTERVAL month, but >= 16th will round-up.

                      DECLARE
                      start_date DATE;
                      end_date DATE;
                      A INTERVAL YEAR TO MONTH;
                      B INTERVAL DAY (4) TO SECOND (9);
                      BEGIN

                      start_date := TO_DATE('05/31/2001','MM/DD/YYYY');
                      end_date := TO_DATE('07/01/2001','MM/DD/YYYY');

                      A :=( end_date - start_date ) YEAR TO MONTH;
                      DBMS_OUTPUT.PUT_LINE( A );
                      DBMS_OUTPUT.PUT_LINE( EXTRACT(YEAR FROM A)||' Years and '|| EXTRACT(MONTH FROM A)||' Months' );

                      B :=( end_date - start_date ) DAY TO SECOND;
                      DBMS_OUTPUT.PUT_LINE( EXTRACT(DAY FROM B)||' Days');
                      END;

                      start_date := TO_DATE('05/31/2001','MM/DD/YYYY');
                      end_date := TO_DATE('06/01/2001','MM/DD/YYYY');

                      output
                      +00-00
                      0 Years and 0 Months
                      1 Days

                      start_date := TO_DATE('05/31/2001','MM/DD/YYYY');
                      end_date := TO_DATE('06/15/2001','MM/DD/YYYY');

                      output
                      +00-00
                      0 Years and 0 Months
                      15 Days

                      start_date := TO_DATE('05/31/2001','MM/DD/YYYY');
                      end_date := TO_DATE('06/16/2001','MM/DD/YYYY');

                      output
                      +00-01
                      0 Years and 1 Months
                      16 Days

                      start_date := TO_DATE('05/31/2001','MM/DD/YYYY');
                      end_date := TO_DATE('06/30/2001','MM/DD/YYYY');

                      output
                      +00-01
                      0 Years and 1 Months
                      30 Days