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!

Need help to resolve ORA-01858: a non-numeric character was found where a numeric was expected

VIAug 20 2018 — edited Aug 20 2018

Hi I have data as below in table. the column data type is varchar2. I am getting "ORA-01858: a non-numeric character was found where a numeric was expected" error if i try to get output for any month but without the error clause the query works fine.

char_value

7/11/1973

4/15/1974

1/20/1975

3/10/1975

6/8/1976

9/20/1976

11/2/1976

2/28/1977

below query not working

select * from

(select to_date(char_value, 'mm/dd/yyyy') service_date,

to_char(to_date(char_value, 'mm/dd/yyyy') , 'mm') as service_month

from user_char)

where service_month = '07'

below query works fine.

select * from

(select to_date(char_value, 'mm/dd/yyyy') service_date,

to_char(to_date(char_value, 'mm/dd/yyyy') , 'mm') as service_month

from user_char)

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 17 2018
Added on Aug 20 2018
11 comments
25,151 views