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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
22,128 views