Home » SQL & PL/SQL » SQL & PL/SQL » invalid number (10.2.0.3, windows)
invalid number [message #571874] Mon, 03 December 2012 05:43 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear All,


select to_char(to_date(mcih_date, 'dd-mm-yyyy')),
mcih_terms from  I_MEMO_CONF_H;

   	TO_CHAR(TO_DATE(MCIH_DATE,'DD-	MCIH_TERMS
1	18-OCT-12	45
2	06-NOV-12	22
3	08-NOV-12	2
4	21-NOV-12	22
5	22-NOV-12	
6	22-NOV-12	2
7	22-NOV-12	2
8	26-NOV-12	1
9	26-NOV-12	22
10	27-NOV-12	1



create table dummy1 (d1 number, d2 varchar2(10));

insert into dummy1 values(1, 4)
insert into dummy1 values(1, 5)

select to_char(to_date(sysdate, 'dd-mm-yyyy')+d2) from dummy1;

   	TO_CHAR(TO_DATE(SYSDATE,'DD-MM
1	07-DEC-12
2	08-DEC-12


select to_char(to_date(mcih_date, 'dd-mm-yyyy')+mcih_terms) from  I_MEMO_CONF_H;

01777- invalid number


why i am getting this error?
Re: invalid number [message #571876 is a reply to message #571874] Mon, 03 December 2012 05:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"to_char(to_date(sysdate, 'dd-mm-yyyy')+d2)",

1/ to_date on a date is just silly
2/ "d2" is not a number it is a string and adding a string to a date is also silly.

Regards
Michel
Re: invalid number [message #571877 is a reply to message #571876] Mon, 03 December 2012 06:14 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Michel,

Appreciate your quick answer.

Your both point is very correct. I have seen the data. All the rows are inserted with number only one row is entered as string which was giving error.

Thanks for your quick responce.

Ishika
Re: invalid number [message #571878 is a reply to message #571877] Mon, 03 December 2012 07:00 Go to previous message
Michel Cadot
Messages: 59748
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A number in a string is NOT a number, it is still a STRING.
NEVER rely on implicit conversions, they may (and sooner or later) will not work as you expect.

Regards
Michel
Previous Topic: Procedure image load problem (2 Merged)
Next Topic: Partition
Goto Forum:
  


Current Time: Sat Nov 22 10:48:43 CST 2014

Total time taken to generate the page: 0.08753 seconds