Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

converting varchar2 to date format

housetiger77Jul 18 2012 — edited Jul 18 2012
I've converted date formats many times, but for some reason I'm getting an invalid number error when trying to convert a varchar2 column. I've tried the to_char and to_date function and I get the same result. The column is a date and it is formatted as DD-MON-YYYY, but I want to change it to MM/DD/YYYY. My query is below:

select to_date('fccpdate','MM/DD/YYYY')
from cc_class_scmast_v

When I try to_date I get this:

Error starting at line 1 in command:
select TO_DATE('fccpdate', 'DD-MON-YYYY') from cc_class_scmast_v where fccpdate IS NOT NULL
Error report:
SQL Error: ORA-01858: a non-numeric character was found where a numeric was expected
01858. 00000 - "a non-numeric character was found where a numeric was expected"
*Cause: The input data to be converted using a date format model was
incorrect. The input data did not contain a number where a number was
required by the format model.
*Action: Fix the input data or the date format model to make sure the
elements match in number and type. Then retry the operation.



When I try to_char I get this:

Error starting at line 1 in command:
select TO_char('fccpdate', 'DD-MON-YYYY') from cc_class_scmast_v where fccpdate IS NOT NULL
Error report:
SQL Error: ORA-01722: invalid number
01722. 00000 - "invalid number"
*Cause:
*Action:


I've tried removing the single quotes from my column and that doesn't make a difference. Any help is appreciated.
This post has been answered by Frank Kulash on Jul 18 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 15 2012
Added on Jul 18 2012
9 comments
1,791 views