Home » SQL & PL/SQL » SQL & PL/SQL » date conversion (oracle 10g)
date conversion [message #352362] Tue, 07 October 2008 06:00 Go to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Hi,
I have a table.
SQL> desc mine_date;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------

 a                                          DATE
 b                                          VARCHAR2(20)



SQL> select * from mine_date;

a         b
--------- --------------------
07-OCT-08 sysdate + 1/1440
07-OCT-08 sysdate + 5/1440



I have to compare the sysdate with the addition of column 'a' to the fraction value in clolumn 'b'.
I am getting an error while converting..
DECLARE
v_a VARCHAR2(100);
v_b DATE;
v_c DATE;
value NUMBER;
BEGIN
SELECT SUBSTR(a,INSTR(a,'+')+1),b INTO v_a, v_b FROM mine_date WHERE ROWNUM<=1;
v_c:=TO_DATE(v_a,'mi');
IF(SYSDATE>(v_b+v_c)) THEN
dbms_output.put_line('here is   '||v_a || v_c);
END IF;
END;
.....................
ERROR at line 9:
ORA-06550: line 9, column 13:
PLS-00306: wrong number or types of arguments in call to '+'
ORA-06550: line 9, column 1:
PL/SQL: Statement ignored



How to convert a fraction(which is in varchar) into minutes.
I have used v_c:=TO_DATE(v_a,'mi'); but getting an error.

Please suggest how to fix these errors.

Regards,
Pointers.

Re: date conversion [message #352364 is a reply to message #352362] Tue, 07 October 2008 06:11 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
 v_c:=TO_DATE(v_a,'mi');


I think you should study TO_CHAR and TO_DATE functions
first.

Regards,
Rajat
Re: date conversion [message #352365 is a reply to message #352362] Tue, 07 October 2008 06:16 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
I know, I must use compatable parameters for date masking.
But i dint fine the format that i can use for fracion with value as 1/1440 etc.

Re: date conversion [message #352367 is a reply to message #352364] Tue, 07 October 2008 06:20 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Your whole code is wrong.

SELECT SUBSTR(a,INSTR(a,'+')+1),b INTO v_a, v_b FROM mine_date WHERE ROWNUM<=1;


v_b is of type date but you are passing a string into it.

DECLARE
 v_a VARCHAR2(100);
 v_b DATE;
 v_c DATE;
 value NUMBER;
BEGIN
 SELECT SUBSTR(a,INSTR(a,'+')+1),b INTO v_a, v_b FROM  
 mine_table WHERE ROWNUM<=1;
END;


It will give you error.

Study dynamic Sql??

Regards,
Rajat Ratewal
Re: date conversion [message #352368 is a reply to message #352362] Tue, 07 October 2008 06:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still the same question and answer than in http://www.orafaq.com/forum/m/351371/102589/#msg_351371
Doesn't it?

Regards
Michel
Re: date conversion [message #352371 is a reply to message #352362] Tue, 07 October 2008 06:32 Go to previous messageGo to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Sorry Micheal I dint find the answer....

Sorry,I have posted the wrong code..

the actual one is..
SQL> desc mine_date;
 Name                                      Null?    Type
 ----------------------------------------- -------- 

b                                          DATE
a                                          VARCHAR2(20)


SQL> select * from mine_date;

b         a
--------- --------------------
07-OCT-08 sysdate + 1/1440
07-OCT-08 sysdate + 5/1440


DECLARE
v_a VARCHAR2(100);
v_b DATE;
v_c DATE;
value NUMBER;
BEGIN
SELECT SUBSTR(a,INSTR(a,'+')+1),b INTO v_a, v_b FROM mine_date WHERE ROWNUM<=1;
v_c:=TO_DATE(v_a,'mi');
IF(SYSDATE>(v_b+v_c)) THEN
dbms_output.put_line('here is   '||v_a || v_c);
END IF;
END;

Re: date conversion [message #352376 is a reply to message #352362] Tue, 07 October 2008 06:49 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Ideal solution will be EXECUTE IMMEDIATE

Thumbs Up
Rajuvan.
Re: date conversion [message #352378 is a reply to message #352371] Tue, 07 October 2008 06:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@pointers, why didn't you continue on the previous topic.

As we all said yo have to use dynamic SQL.

Regards
Michel
Re: date conversion [message #352380 is a reply to message #352371] Tue, 07 October 2008 07:01 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@pointers,

In addition to the other posts,
pointers wrote on Tue, 07 October 2008 17:02

v_b DATE;
v_c DATE;
.
.
.
IF(SYSDATE>(v_b+v_c)) THEN
.
.
END;



I don't believe you can add up two date variables. I guess thats the reason you are getting.
PLS-00306: wrong number or types of arguments in call to '+'

Just a hunch though(I didn't understand the logic you are trying to implement clearly.). Maybe a senior can correct me if I am wrong in my above statements.

Regards,
Jo
Re: date conversion [message #352391 is a reply to message #352362] Tue, 07 October 2008 07:52 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
pointers wrote on Tue, 07 October 2008 07:00


v_b DATE;
v_c DATE;

v_b+v_c




You really have to do some basic problem solving here. What would January 1, 2008 plus March 16, 2008 equal?
Previous Topic: error while inserting into table
Next Topic: Materialized View
Goto Forum:
  


Current Time: Tue Dec 06 02:52:35 CST 2016

Total time taken to generate the page: 0.05035 seconds