Thank's a lot Andrej and Faisal, Resolve the same by the following code: CREATE OR REPLACE FUNCTION NO_DAYS ( M_DATE2 DATE, M_F_VALUE DATE, M_RATE_CONV0 CHAR )
RETURN NUMBER IS V1 NUMBER; V2 NUMBER; V3 NUMBER; V4 NUMBER; V9 NUMBER; MON_BET NUMBER; BEGIN IF trim(replace(replace(M_RATE_CONV0, 'LIN', ''), 'ACT', 'ACTUAL')) ='30/360' THEN v1 := 30- to_char(TO_date(M_F_VALUE,'DD-mm-yyyy'),'dd'); if (v1>=0) then v2 :=v1; else v2 :=0; end if; v3 := to_char(M_DATE2,'dd'); if (v3>30) then v4 :=30; else v4 :=v3; end if; MON_BET := floor(monthS_between((M_DATE2-v4),last_day(M_F_VALUE)))*30; v9 :=v2+v4+MON_BET; ELSE V9 :=M_DATE2 - M_F_VALUE + 1; END IF; RETURN V9; END NO_DAYS; It gives the correct value.Hope i'll receive some suggestions for betterment of the same..Thank's a lot again for help. Regards: Kunal Dube On 4/27/10, mohd faisal <[email protected]> wrote: > little bit correction > 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('30/12/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 > 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) - ( 30 - eddays ); > 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; > > > > On Tue, Apr 27, 2010 at 1:42 PM, mohd faisal <[email protected]> wrote: > >> 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 -- Thanks and Regards Kunal Dube -- 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
