Results 1 to 6 of 6

Thread: date format

  1. #1
    Join Date
    Dec 2003
    Location
    India
    Posts
    33

    Cool Unanswered: date format

    hi all,

    how to change the date format of a database permanently.

    I have tried the following.

    SQL> select sysdate from dual;

    SYSDATE
    ----------
    14-FEB-05

    SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY';

    Session altered.

    SQL> select sysdate from dual;

    SYSDATE
    ----------
    14-02-2005

    Its only for a single session. But, I need the format to change parmanently. Please advice.

    Thanks in advance,
    Papps...

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You could include NLS_DATE_FORMAT='dd-mm-yyyy' into the INIT.ORA file; it'll take effect on the initialization or startup of the instance.

  3. #3
    Join Date
    Dec 2003
    Location
    India
    Posts
    33

    Unhappy

    sorry to inform earlier... I have already set it to the reqd format.
    Also, ALTER SESSION is working for a single session. but if I connect as another session, its coming as follows...

    SQL> show parameter nls_date_format

    NAME TYPE VALUE
    ------------------------------------ ----------- nls_date_format string MM/DD/YYYY

    SQL> select * from v$nls_parameters where parameter = 'NLS_DATE_FORMAT;

    PARAMETER VALUE
    -------------------------------------------------- -------------
    NLS_DATE_FORMAT DD-MON-RR

    Thanks,
    Papps...

  4. #4
    Join Date
    Dec 2004
    Location
    vienna, at
    Posts
    27
    is it possible that some environment variables overrules the instance default?

    the impacts of NLS variables in the environment are complex.
    e.g: when you have set NLS_LANG you change the default for NLS_DATE_FORMAT

    when you want to set NLS_DATE_FORMAT for all your session
    you have to set the NLS_DATE_FORMAT
    Code:
    export NLS_DATE_FORMAT='ddmmyy-hh24:mi:ss'
    before you create a new session
    phh

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    You're right phh about NLS_LANG, I think papps' problem comes from here. NLS_LANG does overrule the init parameter. Here is an excerpt from the "Oracle9i Database Globalization Support Guide", which you can find here :
    Code:
    Table 3-1 Methods of Setting NLS Parameters and Their Priorities 
     
    Priority 	Method
    
    1 (highest)     Explicitly set in SQL functions
    
    2               Set by an ALTER SESSION statement
    
    3               Set as an environment variable
    
    4               Specified in the initialization parameter file
    
    5               Default
    Furthermore, if you follow the link I gave you and go down a little, you will notice that NLS_DATE_FORMAT is "Derived from NLS_TERRITORY", so if you have NLS_LANG environment variable with its TERRITORY portion set (eg : FRENCH_FRANCE.UTF8), it will define the default NLS_DATE_FORMAT unless you change it either at the session level or at the query level.

    HTH & Regards,

    RBARAER

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The other thing is that your client is being run on a windows machine, the NLS format is set in the registry and will need to be changed there.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •