Thread: getting no days problem

getting no days problem

From

sanjeev kumar

Date:

29 January 2009, 09:31:15

Hi all,

I have written procedure as follows:
---------------------------------------------------------
--procedure begin
Create Procedure sp_getNoOfDays(
                         ip_startDate    IN date,
                         ip_endDate      IN date,
                         op_noofdays     OUT bigint
                        )
is
v_DOBMonth        number(20);
v_currMonth       number(20);
v_absMonthsDiff   number(20);
cnt_noOfDays      bigint;
Begin
dbms_output.put_line('1 '||ip_startDate);
dbms_output.put_line('2 '||ip_endDate);
         v_DOBMonth:=to_char(ip_startDate,'MM');
dbms_output.put_line('3 ');
     v_currMonth:=to_char(ip_endDate,'MM');
dbms_output.put_line('4 ');
    v_absMonthsDiff:=abs(to_number(v_currMonth-v_DOBMonth));
dbms_output.put_line('5 '||v_absMonthsDiff);

if v_absMonthsDiff<1
then
dbms_output.put_line('6 ');
select extract(day from ip_endDate)-extract(day from ip_startDate)
into   cnt_noOfDays
from   dual;
dbms_output.put_line('7 ');
else
select ip_endDate-ip_startDate
into cnt_noOfDays
from dual;
--cnt_noOfDays:=0;
end if;

op_noofdays:=cnt_noOfDays;
dbms_output.put_line('8 ');
Exception
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error Message : '||SQLERRM||'Error Code : '||SQLCODE);

End;
--procedure end
---------------------------------------------------------
--Here I am executing

declare
opn bigint;
begin
sp_getNoOfDays('02-Feb-2009','29-Jan-2009',opn);
dbms_output.put_line('No of Days is =>'||opn);
end;
------------------------------------------------------------------
--I am not getting required results but getting error as follows:

INFO: 1 02-FEB-09 00:00:00
INFO: 2 29-JAN-09 00:00:00
INFO: 3
INFO: 4
INFO: 5 1
INFO: Error Message : EDB-22P02: invalid input syntax for integer: "@ 4 days ago"Error Code : 22P02
INFO:
---------------------------------------------
Please any one can help me

--
Thanks & Regards,
-Sanjeev (MIT)

Re: getting no days problem

From

Thom Brown

Date:

29 January 2009, 09:55:31

2009/1/29 sanjeev kumar <vannalaspgsql@gmail.com>

Hi all,

I have written procedure as follows:
---------------------------------------------------------
--procedure begin
Create Procedure sp_getNoOfDays(
                         ip_startDate    IN date,
                         ip_endDate      IN date,
                         op_noofdays     OUT bigint
                        )
i

Create Procedure? Are you sure this isn't for Oracle? Shouldn't this be a function?

Thom

Re: getting no days problem

From

Jasen Betts

Date:

31 January 2009, 17:53:56

On 2009-01-29, sanjeev kumar <vannalaspgsql@gmail.com> wrote:
> --000e0cd150b60728d804619c963d
> Content-Type: text/plain; charset=ISO-8859-1
> Content-Transfer-Encoding: 7bit
>
> Hi all,
>
> I have written procedure as follows:
> ---------------------------------------------------------
> --procedure begin
> Create Procedure sp_getNoOfDays(
>                          ip_startDate    IN date,
>                          ip_endDate      IN date,
>                          op_noofdays     OUT bigint
>                         )

what is it's prupose?