-1

Hi I am using the below query to get the date in the format 'DD-MM-YYYY' from a CLOB field from ORACLE Toad.

 Select ID, NVL(TO_CHAR(xmltype(XML_RAW).extract('//ROWSET//ROW//MJR_V//MJR_V_ROW//EARLIEST_ACCEPT_DATE/text()').getStringVal(), 'DD-MM-YYYY'),'') 
 AS Dateformat from table1 where ID = 102

It thorws error:

ORA:01722: Invalid number

But if I use the above query direct DB columns (NOT CLOB FIELDS) then it executes fine.

 Select ID, NVL(TO_CHAR(Start_Date, 'DD-MM-YYYY'),'') 
 AS Dateformat from table1 where   ID = 102

Please let me the solution.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
tester
  • 263
  • 5
  • 12
  • 27

1 Answers1

0

You shouldn't run to_char on varchar2

to_char function gets a date (or a number) as an argument and converts it to a varchar2.
If you use it on a varchar2, then oracle implicitly converts the string to a date according to NLS_DATE_FORMAT

So you sould probably do something like this:

Community
  • 1
  • 1
A.B.Cade
  • 16,735
  • 1
  • 37
  • 53
  • What did you put as your format in the `to_date` function ? how does `EARLIEST_ACCEPT_DATE` look like in the XML ? – A.B.Cade Dec 20 '12 at 11:23