1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

changing data format in column

Discussion in 'General' started by monkey, Jun 5, 2014.

  1. monkey

    monkey Long Term Member

    Messages:
    156
    Likes Received:
    0
    Trophy Points:
    230
    Hello!
    I have date in the column in format dd-mm-yyy but want to cahnge it to mmddyy

    Please, how to do that and what are keywords?
    Many thanks!!!
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Oracle uses its own internal format to store dates.

    Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.

    The standard Oracle date format is DD-MON-YY (for ex. 01-JAN-14).

    Code (SQL):
    SQL> SELECT sysdate FROM dual;

    SYSDATE
    ------------------
    05-JUN-14
    However, this default date format can be changed for an instance with the parameter NLS_DATE_FORMAT which in your case must be "DD-MM-YYYY".

    You can also change it during a user session with the ALTER SESSION statement.

    Code (SQL):
    SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

    SESSION altered.

    SQL> SELECT sysdate FROM dual;

    SYSDATE
    --------------------
    05-JUN-2014
    You want to change it to mmddyy, you just need to do the following:

    Code (SQL):
    SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYY';

    SESSION altered.

    SQL> SELECT sysdate FROM dual;

    SYSDAT
    ------
    060514
    For more information, please consult:

    http://docs.oracle.com/cd/B19306_01/server.102/b14220/datatype.htm#i1847
     
    monkey likes this.