3 Replies Latest reply: Apr 25, 2013 11:53 AM by S10390 RSS

    date format

      could anyone please let me know how to change date format for this. this is declared as varchar2. and i want in the format of " mm/dd/yyyy"

      0097 1006 ------ yymmdd.
      im using 11g
        • 1. Re: date format
          John Stegeman
          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.
          • 2. Re: date format
            ranit B

            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
                                        replace(dt,' ',''),  -->-- removing WhiteSpaces from the varchar input
                     ) 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.
            • 3. Re: date format
              SQL> SELECT TO_DATE('00971006','YY/MM/DD') DT FROM DUAL;