getting no days problem

From: sanjeev kumar <vannalaspgsql(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: getting no days problem
Date: 2009-01-29 10:31:06
Message-ID: 20fccae00901290231t38dd3244m2531eef95abe0809@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thom Brown 2009-01-29 10:55:21 Re: getting no days problem
Previous Message Ivan Sergio Borgonovo 2009-01-29 09:34:59 Re: very long update gin index troubles back?