declare
todate date;
fromdate date;
no_of_months number(3);
eddays number(3);
stdays number(3);
total_days number(10);
begin
todate := to_date('31/12/2009','dd/mm/yyyy');
fromdate := to_date('31/10/2009','dd/mm/yyyy');
select mod( trunc( months_between( todate, fromdate ) ), 12 ) months
into no_of_months
from dual;
if no_of_months = 0 then
if to_number(to_char(todate,'mm')) = to_number(to_char(fromdate,'mm'))
then
total_days := (30 - to_number(to_char(fromdate,'dd'))) - ( 30 -
to_number(to_char(todate,'dd')));
else
stdays := to_number(to_char(fromdate,'dd'));
eddays := to_number(to_char(todate,'dd'));
if stdays = 31 then
stdays := 30;
end if;
if eddays = 31 then
eddays := 30;
end if;
total_days := (30 - stdays ) + ( eddays);
end if;
else
no_of_months := no_of_months -1;
stdays := to_number(to_char(fromdate,'dd'));
eddays := to_number(to_char(todate,'dd'));
if stdays = 31 then
stdays := 30;
end if;
if eddays = 31 then
eddays := 30;
end if;
total_days := (30 - stdays) + (no_of_months * 30) + eddays;
end if;
dbms_output.put_line(total_days);
end;
looking complicated !!!
On Mon, Apr 26, 2010 at 6:16 PM, Andrej Hopko <[email protected]> wrote:
> Hi,
> fastest way I got is
>
> DECODE(N, 31, 30, N);
>
> when N is number of days in month
> for details just google decode specs
>
> goog luck
>
> Andrej Hopko
>
>
>
> On 26. 4. 2010 12:09, KD wrote:
>
>> Hi all,
>> m trying to write a function which simply returns (Report_date -
>> payment_date) n gives total "NO OF DAYS" but there is one condition
>> that for each month i have to consider 30 days only.
>>
>> Eg. if the Report_date is "31-dec-2009" and payment_date is "30-
>> oct-2009" , then ideally the answer is 62 days,but as per my
>> requirment each month have 30 days only so i have to ignore 31st Oct
>> as well as 31st Dec.so it'll return 60 days only.
>>
>> In other case if the calculation includes month "Feb" then it'll
>> also treated as 30 days month.
>> Can any body help me...
>>
>> Regards:
>> KD
>>
>>
>>
>
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to [email protected]
> To unsubscribe from this group, send email to
> [email protected]
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
>
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
