Oracle invalid number error

Question

This is the query I am using

SELECT TO_DATE(COL1,'mm/dd/yyyy') FROM TABLE1

COL1 is a varchar2(50) column and has date data in the format DD-MON-YY. I want it to be output in 'mm/dd/yyyy' format.

I am getting this 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.

Most of the values in this column are valid dates and I am not able to pin point the record with bad data.

How do I handle this error in my query?

Petr Pribyl · Accepted Answer · 2012-11-16 15:08:34Z

3

SELECT TO_CHAR(TO_DATE(COL1,'dd-mon-yy'), 'mm/dd/yyyy') FROM TABLE1

answered Nov 16, 2012 at 15:08

Petr Pribyl

3,6151 gold badge22 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle invalid number error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related