This discussion is archived
3 Replies Latest reply: Apr 25, 2013 4:53 AM by S10390 RSS

date format

1003231 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    SQL> SELECT TO_DATE('00971006','YY/MM/DD') DT FROM DUAL;

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points