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

    date format

    1003231
      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
            Hi,

            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;
            Output:
            10/06/0097
            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
              S10390
              SQL> SELECT TO_DATE('00971006','YY/MM/DD') DT FROM DUAL;

              DT
              ---------
              06-OCT-97