Re: Oracle Dates
From: ddf <oratune_at_msn.com>
Date: Fri, 8 Jan 2010 06:22:43 -0800 (PST)
Message-ID: <648861bf-eb87-4a59-8ad2-b0da0e12fcb0_at_m3g2000yqf.googlegroups.com>
On Jan 7, 2:04 pm, j.w.vandijk.removet..._at_hetnet.nl (Jaap W. van Dijk) wrote:
> On Thu, 7 Jan 2010 06:07:24 -0800 (PST), ddf <orat..._at_msn.com> wrote:
> >On Jan 6, 3:38=A0pm, j.w.vandijk.removet..._at_hetnet.nl (Jaap W. van Dijk)
> >wrote:
> >> On Tue, 5 Jan 2010 19:30:51 -0800 (PST), ddf <orat..._at_msn.com> wrote:
> >> >On Jan 5, 9:09=3DA0pm, The Magnet <a..._at_unsu.com> wrote:
> >> >> On Jan 5, 6:48=3DA0pm, yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones)
> >> >> wrote:
>
> >> >> > The Magnet (a..._at_unsu.com) wrote:
>
> >> >> > : Is there a way for me to figure out how many days in 3 months? =3D=
> >A0For
>
> >> >> > I can't test this, but my first thought is to try something like thi=
> >s
> >> >> > example for counting the days in Jan Feb and March of this year.
>
> >> >> > =3DA0 =3DA0 =3DA0 =3DA0 number count_days_in_3_months :=3D3D
> >> >> > =3DA0 =3DA0 =3DA0 =3DA0 to_date('1-APR-2010','DD-MON-YYYY')
> >> >> > =3DA0 =3DA0 =3DA0 =3DA0 - to_date('1-JAN-2010','DD-MON-YYYY')
>
> >> >> Yes, but I do not actually know the months. =3DA0All I know is that th=
> >e
> >> >> offset starts with "v_date", and I want to count the days, 3 months
> >> >> forward. =3DA0Hoping not to have to code a bunch of stuff, must be som=
> >e
> >> >> function or something.
>
> >> >add_months(v_date, 3) - v_date
>
> >> >returns the number of days:
>
> >> >SQL> select add_months(sysdate, 3) - sysdate
> >> > =A02 =A0from dual;
>
> >> >ADD_MONTHS(SYSDATE,3)-SYSDATE
> >> >-----------------------------
> >> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 90
>
> >> >SQL> =A0declare
> >> > =A02 =A0 =A0 v_date date:=3D3D to_date('06/01/2010','MM/DD/RRRR');
> >> > =A03 =A0 =A0 v_days number;
> >> > =A04 =A0begin
> >> > =A05 =A0 =A0 select add_months(v_date, 3) - v_date
> >> > =A06 =A0 =A0 into v_days
> >> > =A07 =A0 =A0 from dual;
> >> > =A08 =A0 =A0 dbms_output.put_line(v_days);
> >> > =A09 =A0end;
> >> > 10 /
> >> >92
>
> >> >PL/SQL procedure successfully completed.
>
> >> Be aware though that the ADD_MONTHS function might not work as you
> >> would expect when the months have unequal lengths. Two quiz questions:
>
> >> 1) target month is shorter than source month
>
> >> 30 march =A0-> 30 june
> >> 31 march -> ?
> >> 1 april -> 1 july
>
> >> 2) target month is longer than source month
>
> >> 29 april -> 29 july
> >> 30 april -> ?
> >> 1 may -> 1 august
>
> >> Oracle rule: the last day of the source month always projects on the
> >> last day of the target months, so the first question mark is 30 june
> >> and the second question mark is 31 july!
>
> >> I'm not aware of any ISO-definition, and I've seen ADD_MONTHS behave
> >> differently in other products.
>
> >> Regards,
> >> Jaap.
>
> >> >SQL>
>
> >> >David Fitzjarrell- Hide quoted text -
>
> >> - Show quoted text -- Hide quoted text -
>
> >> - Show quoted text -
>
> > 1 declare
> > 2 v_date date:=3D to_date('01/01/2004','MM/DD/RRRR');
> > 3 v_days number;
> > 4 begin
> > 5 for loopctr in 1..100 loop
> > 6 select add_months(v_date, 3) - v_date
> > 7 into v_days
> > 8 from dual;
> > 9 dbms_output.put_line(v_date||' plus three months adds '||
> >v_days||' days');
> > 10 v_date :=3D add_months(v_date, 1);
> > 11 end loop;
> > 12* end;
> >SQL> /
> >01-JAN-04 plus three months adds 91 days
> >01-FEB-04 plus three months adds 90 days
> >01-MAR-04 plus three months adds 92 days
> >01-APR-04 plus three months adds 91 days
> >01-MAY-04 plus three months adds 92 days
> >01-JUN-04 plus three months adds 92 days
> >01-JUL-04 plus three months adds 92 days
> >01-AUG-04 plus three months adds 92 days
> >01-SEP-04 plus three months adds 91 days
> >01-OCT-04 plus three months adds 92 days
> >01-NOV-04 plus three months adds 92 days
> >01-DEC-04 plus three months adds 90 days
> >01-JAN-05 plus three months adds 90 days
> >01-FEB-05 plus three months adds 89 days
> >01-MAR-05 plus three months adds 92 days
> >01-APR-05 plus three months adds 91 days
> >01-MAY-05 plus three months adds 92 days
> >01-JUN-05 plus three months adds 92 days
> >01-JUL-05 plus three months adds 92 days
> >01-AUG-05 plus three months adds 92 days
> >01-SEP-05 plus three months adds 91 days
> >01-OCT-05 plus three months adds 92 days
> >01-NOV-05 plus three months adds 92 days
> >01-DEC-05 plus three months adds 90 days
> >01-JAN-06 plus three months adds 90 days
> >01-FEB-06 plus three months adds 89 days
> >01-MAR-06 plus three months adds 92 days
> >01-APR-06 plus three months adds 91 days
> >01-MAY-06 plus three months adds 92 days
> >01-JUN-06 plus three months adds 92 days
> >01-JUL-06 plus three months adds 92 days
> >01-AUG-06 plus three months adds 92 days
> >01-SEP-06 plus three months adds 91 days
> >01-OCT-06 plus three months adds 92 days
> >01-NOV-06 plus three months adds 92 days
> >01-DEC-06 plus three months adds 90 days
> >01-JAN-07 plus three months adds 90 days
> >01-FEB-07 plus three months adds 89 days
> >01-MAR-07 plus three months adds 92 days
> >01-APR-07 plus three months adds 91 days
> >01-MAY-07 plus three months adds 92 days
> >01-JUN-07 plus three months adds 92 days
> >01-JUL-07 plus three months adds 92 days
> >01-AUG-07 plus three months adds 92 days
> >01-SEP-07 plus three months adds 91 days
> >01-OCT-07 plus three months adds 92 days
> >01-NOV-07 plus three months adds 92 days
> >01-DEC-07 plus three months adds 91 days
> >01-JAN-08 plus three months adds 91 days
> >01-FEB-08 plus three months adds 90 days
> >01-MAR-08 plus three months adds 92 days
> >01-APR-08 plus three months adds 91 days
> >01-MAY-08 plus three months adds 92 days
> >01-JUN-08 plus three months adds 92 days
> >01-JUL-08 plus three months adds 92 days
> >01-AUG-08 plus three months adds 92 days
> >01-SEP-08 plus three months adds 91 days
> >01-OCT-08 plus three months adds 92 days
> >01-NOV-08 plus three months adds 92 days
> >01-DEC-08 plus three months adds 90 days
> >01-JAN-09 plus three months adds 90 days
> >01-FEB-09 plus three months adds 89 days
> >01-MAR-09 plus three months adds 92 days
> >01-APR-09 plus three months adds 91 days
> >01-MAY-09 plus three months adds 92 days
> >01-JUN-09 plus three months adds 92 days
> >01-JUL-09 plus three months adds 92 days
> >01-AUG-09 plus three months adds 92 days
> >01-SEP-09 plus three months adds 91 days
> >01-OCT-09 plus three months adds 92 days
> >01-NOV-09 plus three months adds 92 days
> >01-DEC-09 plus three months adds 90 days
> >01-JAN-10 plus three months adds 90 days
> >01-FEB-10 plus three months adds 89 days
> >01-MAR-10 plus three months adds 92 days
> >01-APR-10 plus three months adds 91 days
> >01-MAY-10 plus three months adds 92 days
> >01-JUN-10 plus three months adds 92 days
> >01-JUL-10 plus three months adds 92 days
> >01-AUG-10 plus three months adds 92 days
> >01-SEP-10 plus three months adds 91 days
> >01-OCT-10 plus three months adds 92 days
> >01-NOV-10 plus three months adds 92 days
> >01-DEC-10 plus three months adds 90 days
> >01-JAN-11 plus three months adds 90 days
> >01-FEB-11 plus three months adds 89 days
> >01-MAR-11 plus three months adds 92 days
> >01-APR-11 plus three months adds 91 days
> >01-MAY-11 plus three months adds 92 days
> >01-JUN-11 plus three months adds 92 days
> >01-JUL-11 plus three months adds 92 days
> >01-AUG-11 plus three months adds 92 days
> >01-SEP-11 plus three months adds 91 days
> >01-OCT-11 plus three months adds 92 days
> >01-NOV-11 plus three months adds 92 days
> >01-DEC-11 plus three months adds 91 days
> >01-JAN-12 plus three months adds 91 days
> >01-FEB-12 plus three months adds 90 days
> >01-MAR-12 plus three months adds 92 days
> >01-APR-12 plus three months adds 91 days
>
> >PL/SQL procedure successfully completed.
>
> >SQL>
>
> >I'm not seeing the problem you mention with this function; it looks
> >pretty reliable to me.
>
> >David Fitzjarrell
>
> That's true if you start on the first day of the month. From the OP's
> description I gathered it could be any day of the month. And then:
>
> declare
> v_date date;
> begin
> v_date := to_date('30-03-2009', 'dd-mm-yyyy');
> dbms_output.put_line(to_char(v_date,'dd-mm-yyyy')||' plus three months
> adds '||to_char(add_months(v_date,3) - v_date)||' days');
> v_date := to_date('31-03-2009', 'dd-mm-yyyy');
> dbms_output.put_line(to_char(v_date,'dd-mm-yyyy')||' plus three months
> adds '||to_char(add_months(v_date,3) - v_date)||' days');
> v_date := to_date('01-04-2009', 'dd-mm-yyyy');
> dbms_output.put_line(to_char(v_date,'dd-mm-yyyy')||' plus three months
> adds '||to_char(add_months(v_date,3) - v_date)||' days');
> v_date := to_date('29-04-2009', 'dd-mm-yyyy');
> dbms_output.put_line(to_char(v_date,'dd-mm-yyyy')||' plus three months
> adds '||to_char(add_months(v_date,3) - v_date)||' days');
> v_date := to_date('30-04-2009', 'dd-mm-yyyy');
> dbms_output.put_line(to_char(v_date,'dd-mm-yyyy')||' plus three months
> adds '||to_char(add_months(v_date,3) - v_date)||' days');
> v_date := to_date('01-05-2009', 'dd-mm-yyyy');
> dbms_output.put_line(to_char(v_date,'dd-mm-yyyy')||' plus three months
> adds '||to_char(add_months(v_date,3) - v_date)||' days');
> end;
> /
> 30-03-2009 plus three months adds 92 days
> 31-03-2009 plus three months adds 91 days
> 01-04-2009 plus three months adds 91 days
> 29-04-2009 plus three months adds 91 days
> 30-04-2009 plus three months adds 92 days
> 01-05-2009 plus three months adds 92 days
>
> The difference (91/92) between 29-04-2009 and 30-04-2009 can be
> somewhat surprising at first.
>
> Regards,
> Jaap.- Hide quoted text -
>
> - Show quoted text -
Date: Fri, 8 Jan 2010 06:22:43 -0800 (PST)
Message-ID: <648861bf-eb87-4a59-8ad2-b0da0e12fcb0_at_m3g2000yqf.googlegroups.com>
On Jan 7, 2:04 pm, j.w.vandijk.removet..._at_hetnet.nl (Jaap W. van Dijk) wrote:
> On Thu, 7 Jan 2010 06:07:24 -0800 (PST), ddf <orat..._at_msn.com> wrote:
> >On Jan 6, 3:38=A0pm, j.w.vandijk.removet..._at_hetnet.nl (Jaap W. van Dijk)
> >wrote:
> >> On Tue, 5 Jan 2010 19:30:51 -0800 (PST), ddf <orat..._at_msn.com> wrote:
> >> >On Jan 5, 9:09=3DA0pm, The Magnet <a..._at_unsu.com> wrote:
> >> >> On Jan 5, 6:48=3DA0pm, yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones)
> >> >> wrote:
>
> >> >> > The Magnet (a..._at_unsu.com) wrote:
>
> >> >> > : Is there a way for me to figure out how many days in 3 months? =3D=
> >A0For
>
> >> >> > I can't test this, but my first thought is to try something like thi=
> >s
> >> >> > example for counting the days in Jan Feb and March of this year.
>
> >> >> > =3DA0 =3DA0 =3DA0 =3DA0 number count_days_in_3_months :=3D3D
> >> >> > =3DA0 =3DA0 =3DA0 =3DA0 to_date('1-APR-2010','DD-MON-YYYY')
> >> >> > =3DA0 =3DA0 =3DA0 =3DA0 - to_date('1-JAN-2010','DD-MON-YYYY')
>
> >> >> Yes, but I do not actually know the months. =3DA0All I know is that th=
> >e
> >> >> offset starts with "v_date", and I want to count the days, 3 months
> >> >> forward. =3DA0Hoping not to have to code a bunch of stuff, must be som=
> >e
> >> >> function or something.
>
> >> >add_months(v_date, 3) - v_date
>
> >> >returns the number of days:
>
> >> >SQL> select add_months(sysdate, 3) - sysdate
> >> > =A02 =A0from dual;
>
> >> >ADD_MONTHS(SYSDATE,3)-SYSDATE
> >> >-----------------------------
> >> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 90
>
> >> >SQL> =A0declare
> >> > =A02 =A0 =A0 v_date date:=3D3D to_date('06/01/2010','MM/DD/RRRR');
> >> > =A03 =A0 =A0 v_days number;
> >> > =A04 =A0begin
> >> > =A05 =A0 =A0 select add_months(v_date, 3) - v_date
> >> > =A06 =A0 =A0 into v_days
> >> > =A07 =A0 =A0 from dual;
> >> > =A08 =A0 =A0 dbms_output.put_line(v_days);
> >> > =A09 =A0end;
> >> > 10 /
> >> >92
>
> >> >PL/SQL procedure successfully completed.
>
> >> Be aware though that the ADD_MONTHS function might not work as you
> >> would expect when the months have unequal lengths. Two quiz questions:
>
> >> 1) target month is shorter than source month
>
> >> 30 march =A0-> 30 june
> >> 31 march -> ?
> >> 1 april -> 1 july
>
> >> 2) target month is longer than source month
>
> >> 29 april -> 29 july
> >> 30 april -> ?
> >> 1 may -> 1 august
>
> >> Oracle rule: the last day of the source month always projects on the
> >> last day of the target months, so the first question mark is 30 june
> >> and the second question mark is 31 july!
>
> >> I'm not aware of any ISO-definition, and I've seen ADD_MONTHS behave
> >> differently in other products.
>
> >> Regards,
> >> Jaap.
>
> >> >SQL>
>
> >> >David Fitzjarrell- Hide quoted text -
>
> >> - Show quoted text -- Hide quoted text -
>
> >> - Show quoted text -
>
> > 1 declare
> > 2 v_date date:=3D to_date('01/01/2004','MM/DD/RRRR');
> > 3 v_days number;
> > 4 begin
> > 5 for loopctr in 1..100 loop
> > 6 select add_months(v_date, 3) - v_date
> > 7 into v_days
> > 8 from dual;
> > 9 dbms_output.put_line(v_date||' plus three months adds '||
> >v_days||' days');
> > 10 v_date :=3D add_months(v_date, 1);
> > 11 end loop;
> > 12* end;
> >SQL> /
> >01-JAN-04 plus three months adds 91 days
> >01-FEB-04 plus three months adds 90 days
> >01-MAR-04 plus three months adds 92 days
> >01-APR-04 plus three months adds 91 days
> >01-MAY-04 plus three months adds 92 days
> >01-JUN-04 plus three months adds 92 days
> >01-JUL-04 plus three months adds 92 days
> >01-AUG-04 plus three months adds 92 days
> >01-SEP-04 plus three months adds 91 days
> >01-OCT-04 plus three months adds 92 days
> >01-NOV-04 plus three months adds 92 days
> >01-DEC-04 plus three months adds 90 days
> >01-JAN-05 plus three months adds 90 days
> >01-FEB-05 plus three months adds 89 days
> >01-MAR-05 plus three months adds 92 days
> >01-APR-05 plus three months adds 91 days
> >01-MAY-05 plus three months adds 92 days
> >01-JUN-05 plus three months adds 92 days
> >01-JUL-05 plus three months adds 92 days
> >01-AUG-05 plus three months adds 92 days
> >01-SEP-05 plus three months adds 91 days
> >01-OCT-05 plus three months adds 92 days
> >01-NOV-05 plus three months adds 92 days
> >01-DEC-05 plus three months adds 90 days
> >01-JAN-06 plus three months adds 90 days
> >01-FEB-06 plus three months adds 89 days
> >01-MAR-06 plus three months adds 92 days
> >01-APR-06 plus three months adds 91 days
> >01-MAY-06 plus three months adds 92 days
> >01-JUN-06 plus three months adds 92 days
> >01-JUL-06 plus three months adds 92 days
> >01-AUG-06 plus three months adds 92 days
> >01-SEP-06 plus three months adds 91 days
> >01-OCT-06 plus three months adds 92 days
> >01-NOV-06 plus three months adds 92 days
> >01-DEC-06 plus three months adds 90 days
> >01-JAN-07 plus three months adds 90 days
> >01-FEB-07 plus three months adds 89 days
> >01-MAR-07 plus three months adds 92 days
> >01-APR-07 plus three months adds 91 days
> >01-MAY-07 plus three months adds 92 days
> >01-JUN-07 plus three months adds 92 days
> >01-JUL-07 plus three months adds 92 days
> >01-AUG-07 plus three months adds 92 days
> >01-SEP-07 plus three months adds 91 days
> >01-OCT-07 plus three months adds 92 days
> >01-NOV-07 plus three months adds 92 days
> >01-DEC-07 plus three months adds 91 days
> >01-JAN-08 plus three months adds 91 days
> >01-FEB-08 plus three months adds 90 days
> >01-MAR-08 plus three months adds 92 days
> >01-APR-08 plus three months adds 91 days
> >01-MAY-08 plus three months adds 92 days
> >01-JUN-08 plus three months adds 92 days
> >01-JUL-08 plus three months adds 92 days
> >01-AUG-08 plus three months adds 92 days
> >01-SEP-08 plus three months adds 91 days
> >01-OCT-08 plus three months adds 92 days
> >01-NOV-08 plus three months adds 92 days
> >01-DEC-08 plus three months adds 90 days
> >01-JAN-09 plus three months adds 90 days
> >01-FEB-09 plus three months adds 89 days
> >01-MAR-09 plus three months adds 92 days
> >01-APR-09 plus three months adds 91 days
> >01-MAY-09 plus three months adds 92 days
> >01-JUN-09 plus three months adds 92 days
> >01-JUL-09 plus three months adds 92 days
> >01-AUG-09 plus three months adds 92 days
> >01-SEP-09 plus three months adds 91 days
> >01-OCT-09 plus three months adds 92 days
> >01-NOV-09 plus three months adds 92 days
> >01-DEC-09 plus three months adds 90 days
> >01-JAN-10 plus three months adds 90 days
> >01-FEB-10 plus three months adds 89 days
> >01-MAR-10 plus three months adds 92 days
> >01-APR-10 plus three months adds 91 days
> >01-MAY-10 plus three months adds 92 days
> >01-JUN-10 plus three months adds 92 days
> >01-JUL-10 plus three months adds 92 days
> >01-AUG-10 plus three months adds 92 days
> >01-SEP-10 plus three months adds 91 days
> >01-OCT-10 plus three months adds 92 days
> >01-NOV-10 plus three months adds 92 days
> >01-DEC-10 plus three months adds 90 days
> >01-JAN-11 plus three months adds 90 days
> >01-FEB-11 plus three months adds 89 days
> >01-MAR-11 plus three months adds 92 days
> >01-APR-11 plus three months adds 91 days
> >01-MAY-11 plus three months adds 92 days
> >01-JUN-11 plus three months adds 92 days
> >01-JUL-11 plus three months adds 92 days
> >01-AUG-11 plus three months adds 92 days
> >01-SEP-11 plus three months adds 91 days
> >01-OCT-11 plus three months adds 92 days
> >01-NOV-11 plus three months adds 92 days
> >01-DEC-11 plus three months adds 91 days
> >01-JAN-12 plus three months adds 91 days
> >01-FEB-12 plus three months adds 90 days
> >01-MAR-12 plus three months adds 92 days
> >01-APR-12 plus three months adds 91 days
>
> >PL/SQL procedure successfully completed.
>
> >SQL>
>
> >I'm not seeing the problem you mention with this function; it looks
> >pretty reliable to me.
>
> >David Fitzjarrell
>
> That's true if you start on the first day of the month. From the OP's
> description I gathered it could be any day of the month. And then:
>
> declare
> v_date date;
> begin
> v_date := to_date('30-03-2009', 'dd-mm-yyyy');
> dbms_output.put_line(to_char(v_date,'dd-mm-yyyy')||' plus three months
> adds '||to_char(add_months(v_date,3) - v_date)||' days');
> v_date := to_date('31-03-2009', 'dd-mm-yyyy');
> dbms_output.put_line(to_char(v_date,'dd-mm-yyyy')||' plus three months
> adds '||to_char(add_months(v_date,3) - v_date)||' days');
> v_date := to_date('01-04-2009', 'dd-mm-yyyy');
> dbms_output.put_line(to_char(v_date,'dd-mm-yyyy')||' plus three months
> adds '||to_char(add_months(v_date,3) - v_date)||' days');
> v_date := to_date('29-04-2009', 'dd-mm-yyyy');
> dbms_output.put_line(to_char(v_date,'dd-mm-yyyy')||' plus three months
> adds '||to_char(add_months(v_date,3) - v_date)||' days');
> v_date := to_date('30-04-2009', 'dd-mm-yyyy');
> dbms_output.put_line(to_char(v_date,'dd-mm-yyyy')||' plus three months
> adds '||to_char(add_months(v_date,3) - v_date)||' days');
> v_date := to_date('01-05-2009', 'dd-mm-yyyy');
> dbms_output.put_line(to_char(v_date,'dd-mm-yyyy')||' plus three months
> adds '||to_char(add_months(v_date,3) - v_date)||' days');
> end;
> /
> 30-03-2009 plus three months adds 92 days
> 31-03-2009 plus three months adds 91 days
> 01-04-2009 plus three months adds 91 days
> 29-04-2009 plus three months adds 91 days
> 30-04-2009 plus three months adds 92 days
> 01-05-2009 plus three months adds 92 days
>
> The difference (91/92) between 29-04-2009 and 30-04-2009 can be
> somewhat surprising at first.
>
> Regards,
> Jaap.- Hide quoted text -
>
> - Show quoted text -
I will agree as it appears add_months does not behave consistently when approaching the end of the starting month:
declare
v_date date;
begin
v_date := to_date('30-03-2009', 'dd-mm-yyyy');
dbms_output.put_line(to_char(v_date,'dd-mm-yyyy')||' plus three months
becomes '||to_char(add_months(v_date,3),'mm/dd/rrrr')||' and adds '||
to_char(add_months(v_date,3) - v_date)||' days');
v_date := to_date('31-03-2009', 'dd-mm-yyyy');
dbms_output.put_line(to_char(v_date,'dd-mm-yyyy')||' plus three months
becomes '||to_char(add_months(v_date,3),'mm/dd/rrrr')||' and adds '||
to_char(add_months(v_date,3) - v_date)||' days');
v_date := to_date('01-04-2009', 'dd-mm-yyyy');
dbms_output.put_line(to_char(v_date,'dd-mm-yyyy')||' plus three months
becomes '||to_char(add_months(v_date,3),'mm/dd/rrrr')||' and adds '||
to_char(add_months(v_date,3) - v_date)||' days');
v_date := to_date('29-04-2009', 'dd-mm-yyyy');
dbms_output.put_line(to_char(v_date,'dd-mm-yyyy')||' plus three months
becomes '||to_char(add_months(v_date,3),'mm/dd/rrrr')||' and adds '||
to_char(add_months(v_date,3) - v_date)||' days');
v_date := to_date('30-04-2009', 'dd-mm-yyyy');
dbms_output.put_line(to_char(v_date,'dd-mm-yyyy')||' plus three months
becomes '||to_char(add_months(v_date,3),'mm/dd/rrrr')||' and adds '||
to_char(add_months(v_date,3) - v_date)||' days');
v_date := to_date('01-05-2009', 'dd-mm-yyyy');
dbms_output.put_line(to_char(v_date,'dd-mm-yyyy')||' plus three months
becomes '||to_char(add_months(v_date,3),'mm/dd/rrrr')||' and adds '||
to_char(add_months(v_date,3) - v_date)||' days');
end;
/
produces:
30-03-2009 plus three months becomes 06/30/2009 and adds 92 days 31-03-2009 plus three months becomes 06/30/2009 and adds 91 days 01-04-2009 plus three months becomes 07/01/2009 and adds 91 days 29-04-2009 plus three months becomes 07/29/2009 and adds 91 days 30-04-2009 plus three months becomes 07/31/2009 and adds 92 days 01-05-2009 plus three months becomes 08/01/2009 and adds 92 days
To go to a 30-day month from a 31-day month makes sense, but to go to a 31-day month from the end of a 30-day month does not (in my mind, anyway) as the 'destination' date should be 07/30/2009, not 07/31/2009 as illustrated.
David Fitzjarrell Received on Fri Jan 08 2010 - 08:22:43 CST