Re: Oracle Dates
Date: Wed, 06 Jan 2010 20:38:53 GMT
Message-ID: <4b44f293.617671_at_news.hetnet.nl>
On Tue, 5 Jan 2010 19:30:51 -0800 (PST), ddf <oratune_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:
- 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
Received on Wed Jan 06 2010 - 14:38:53 CST