'Facing an issue while converting invalid date string to valid date string in SQL11g getting error ORA-01858
I am facing an issue when I was converting invalid date format to valid date format using the TO_DATE function, Here I explained my problem in the easiest way possible but in reality, the data is huge and having this problem. If you can provide me with a solution to this problem then it will be much helpful. I tried this ->
Select TO_DATE('TWENTY-THREE,JANUARY,1998' , 'FMDDSP,MONTH,YYYY') From DUAL;
As I am having input date string as - DD in spelled format as you can see i.e. twenty-three, twenty-four. I want to convert that into valid date format to dd-mon-yy So that I can store them into the database. Right now I am getting Error - ERROR at line 1: ORA-01858: a non-numeric character was found where a numeric was expected. I am using oracle 11g (SQL*plus)
Solution 1:[1]
Sorry, afaik TO_DATE does not support the conversion from format sp or spth. That only works the other way round, with TO_CHAR. So you are left here with the only option of writing your own conversion function in PL/SQL. or Java.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |