This content has been marked as final. Show 3 replies
Take a step back and re-read your question from the viewpoint of someone who knows nothing about your problem and ask yourself "does this make any sense?"
I did, and my answer was "no"
What is "0097 1006?" It doesn't match either format you gave us.
As you've been told before, use the DATE datatype for storing, well, DATEs. Not varchar2.
As John already pointed out, you should DATE datatype to store DATES and nothing else.
0097 1006 : yymmdd.I tried understanding this by relating your format 'yymmdd' with the input '0097 1006', I guess_ you mean this :
-- "input" yy = 97 /* YEAR : prefixed with '00' but why is still unclear */ mm = 10 /* MONTH */ dd = 06 /* DAY */ -- "query" with xx as ( select '0097 1006' dt from dual ) select TO_CHAR( TO_DATE( replace(dt,' ',''), -->-- removing WhiteSpaces from the varchar input 'yyyymmdd' ), 'mm/dd/yyyy' ) dtx from xx;
It's still unclear to me that why you have year as '0097' ??? What is the significance of '00'?
Please let us know if this meets your requirement.