Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ORA-01722: invalid number

Posted on 2010-11-18
5
Medium Priority
?
1,936 Views
Last Modified: 2013-12-07
why am i getting this error?
select to_char('10/15/2010 11:07:20 AM', 'mm/dd/yyyy hh:mi:ss AM') from dual;

ORA-01722: invalid number
0
Comment
Question by:gram77
  • 3
  • 2
5 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 34164177
you need to use to_char with a date not a raw string.

select to_date('10/15/2010 11:07:20 AM', 'mm/dd/yyyy hh:mi:ss AM') from dual;

0
 
LVL 1

Expert Comment

by:Lacca
ID: 34165248
Because '10/15/2010 11:07:20 AM' already is a char.

Worging Examples:

convert chat to date
select to_date('10/15/2010 11:07:20 AM', 'mm/dd/yyyy hh:mi:ss AM') from dual;

convert date to char
select to_char(SYSDATE, 'mm/dd/yyyy hh:mi:ss AM') from dual;

0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 34165300
>>Because '10/15/2010 11:07:20 AM' already is a char.

To further clarify:  Actually you can call to_char with a char.

"select to_char('10/15/2010 11:07:20 AM') from dual;" will run just fine.  The invalid number error comes from Oracle dates being stored internally as a number and the to_char call referenced is expecting an Oracle date, a number, as the first parameter.
0
 
LVL 1

Expert Comment

by:Lacca
ID: 34171556
>>"select to_char('10/15/2010 11:07:20 AM') from dual;"

But what is the purpose of doing this?

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34172599
>>But what is the purpose of doing this?

There would be no purpose.  I was just stating that the error wasn't caused by "Because '10/15/2010 11:07:20 AM' already is a char".  It was caused because '10/15/2010 11:07:20 AM' was not a number.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question