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)
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? |