Re: Oracle Dates

From: Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl>
Date: Thu, 07 Jan 2010 19:04:53 GMT
Message-ID: <4b462c8a.2667250_at_news.hetnet.nl>

On Thu, 7 Jan 2010 06:07:24 -0800 (PST), ddf <oratune_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. Received on Thu Jan 07 2010 - 13:04:53 CST

This message: [ Message body ]
Next message: Mladen Gogala: "Re: Extracting 2.5 million rows"
Previous message: Jonathan Lewis: "Re: Extracting 2.5 million rows"
Maybe in reply to: The Magnet: "Oracle Dates"
Next in thread: ddf: "Re: Oracle Dates"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message