Re: Oracle Dates
From: ddf <oratune_at_msn.com>
Date: Thu, 7 Jan 2010 06:07:24 -0800 (PST)
Message-ID: <ac8168ad-91b6-4e7e-a59c-fa02d8cc5bfa_at_k19g2000yqc.googlegroups.com>
On Jan 6, 3:38 pm, 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=A0pm, The Magnet <a..._at_unsu.com> wrote:
> >> On Jan 5, 6:48=A0pm, 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? =A0For
>
> >> > I can't test this, but my first thought is to try something like this
> >> > example for counting the days in Jan Feb and March of this year.
>
> >> > =A0 =A0 =A0 =A0 number count_days_in_3_months :=3D
> >> > =A0 =A0 =A0 =A0 to_date('1-APR-2010','DD-MON-YYYY')
> >> > =A0 =A0 =A0 =A0 - to_date('1-JAN-2010','DD-MON-YYYY')
>
> >> Yes, but I do not actually know the months. =A0All I know is that the
> >> offset starts with "v_date", and I want to count the days, 3 months
> >> forward. =A0Hoping not to have to code a bunch of stuff, must be some
> >> function or something.
>
> >add_months(v_date, 3) - v_date
>
> >returns the number of days:
>
> >SQL> select add_months(sysdate, 3) - sysdate
> > 2 from dual;
>
> >ADD_MONTHS(SYSDATE,3)-SYSDATE
> >-----------------------------
> > 90
>
> >SQL> declare
> > 2 v_date date:=3D to_date('06/01/2010','MM/DD/RRRR');
> > 3 v_days number;
> > 4 begin
> > 5 select add_months(v_date, 3) - v_date
> > 6 into v_days
> > 7 from dual;
> > 8 dbms_output.put_line(v_days);
> > 9 end;
> > 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 -> 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 -
12* end;
SQL> /
Date: Thu, 7 Jan 2010 06:07:24 -0800 (PST)
Message-ID: <ac8168ad-91b6-4e7e-a59c-fa02d8cc5bfa_at_k19g2000yqc.googlegroups.com>
On Jan 6, 3:38 pm, 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=A0pm, The Magnet <a..._at_unsu.com> wrote:
> >> On Jan 5, 6:48=A0pm, 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? =A0For
>
> >> > I can't test this, but my first thought is to try something like this
> >> > example for counting the days in Jan Feb and March of this year.
>
> >> > =A0 =A0 =A0 =A0 number count_days_in_3_months :=3D
> >> > =A0 =A0 =A0 =A0 to_date('1-APR-2010','DD-MON-YYYY')
> >> > =A0 =A0 =A0 =A0 - to_date('1-JAN-2010','DD-MON-YYYY')
>
> >> Yes, but I do not actually know the months. =A0All I know is that the
> >> offset starts with "v_date", and I want to count the days, 3 months
> >> forward. =A0Hoping not to have to code a bunch of stuff, must be some
> >> function or something.
>
> >add_months(v_date, 3) - v_date
>
> >returns the number of days:
>
> >SQL> select add_months(sysdate, 3) - sysdate
> > 2 from dual;
>
> >ADD_MONTHS(SYSDATE,3)-SYSDATE
> >-----------------------------
> > 90
>
> >SQL> declare
> > 2 v_date date:=3D to_date('06/01/2010','MM/DD/RRRR');
> > 3 v_days number;
> > 4 begin
> > 5 select add_months(v_date, 3) - v_date
> > 6 into v_days
> > 7 from dual;
> > 8 dbms_output.put_line(v_days);
> > 9 end;
> > 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 -> 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:= 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 := 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 Received on Thu Jan 07 2010 - 08:07:24 CST