Follow by Email

Wednesday, July 9, 2014

ORA-01858: a non-numeric character was found where a numeric was expected

I was getting this problesm while I was trying to debug PLSQL code written by someone else . And I found the sql statement like below where he was trying to apply to_date function on a date type column ,

The format of MY_DATE_COLUMN is like "09-JULY-2014" , so when to-date function was applied on this date format to convert it to MM/DD/YYYY oracle was throwing this error

SELECT TO_DATE(MY_DATE_COLUMN, 'MM/DD/YYYY') - INTERVAL '5' YEAR FROM DUAL

The solutionw was simple , to get rid of the to_date function , so the below query is working fine for me now.

SELECT MY_DATE_COLUMN - INTERVAL '5' YEAR FROM DUAL



No comments:

Post a Comment