UNABLE to change NLS_DATE_FORMAT. Please HELP
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: UNABLE to change NLS_DATE_FORMAT. Please HELP

  1. #1
    Join Date
    Mar 2002
    Posts
    200

    UNABLE to change NLS_DATE_FORMAT. Please HELP

    I am using Oracle 9i and am issuing this command -

    alter system set nls_date_format = 'YYYY/MM/DD';

    I have logged in through SQL PLUS as - Conn SYS as SYSDBA.

    I am getting the the following error:

    ERROR at line 1:
    ORA-02096: specified initialization parameter is not modifiable with this option


    I even tried -

    alter database set nls_date_format = 'YYYY/MM/DD';

    It gives the following error -


    ERROR at line 1:
    ORA-02231: missing or invalid option to ALTER DATABASE



    Please help, as I need to change the date format at the database level.
    Last edited by quester; 11-27-2002 at 02:04 AM.

  2. #2
    Join Date
    Mar 2002
    Posts
    200
    I know I can set the parameter in the InitSID.Ora file and restart the database; I want to avoid this and would like to do this at the command level. Please help.

  3. #3
    Join Date
    Feb 2001
    Posts
    180
    In this case you can only do:
    ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD';
    =======
    Regards
    Ben de Boer

  4. #4
    Join Date
    Oct 2002
    Location
    Singapore
    Posts
    16
    Still if you want this to appear in client m/c's as well, then you have to edit the registry settings to change the NLS_DATE_FORMAT in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE. This will always take priority over the server settings.
    Never Ever Give Up!

  5. #5
    Join Date
    Mar 2002
    Posts
    200
    Originally posted by eeswar
    Still if you want this to appear in client m/c's as well, then you have to edit the registry settings to change the NLS_DATE_FORMAT in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE. This will always take priority over the server settings.
    This only applies on the local machine, right? Not for the entire server....in which case alter session can do. I needed to change on the database level, not on the local machine or session.

  6. #6
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by quester
    This only applies on the local machine, right? Not for the entire server....in which case alter session can do. I needed to change on the database level, not on the local machine or session.
    At database level, modify init.ora and bounce the instance. You can't change it on fly.

    Sanjay

  7. #7
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hi,
    I tried this too : ALTER SYYTEM ...
    But you cannot do this because Oracle should not and cannot
    change the behavior of the date-format in running sessions!
    The result would be not consistent.

    Orca

  8. #8
    Join Date
    Sep 2001
    Posts
    120
    From the Oracle DOCS

    NLS_DATE_FORMAT
    Parameter type String

    Syntax NLS_DATE_FORMAT = "format"

    Default value Derived from NLS_TERRITORY

    Parameter class Dynamic: ALTER SESSION

    Range of values Any valid date format mask but not exceeding a fixed length




    NLS_DATE_FORMAT specifies the default date format to use with the TO_CHAR and TO_DATE functions. The default value of this parameter is determined by NLS_TERRITORY.

    The value of this parameter can be any valid date format mask, and the value must be surrounded by double quotation marks. For example:

    NLS_DATE_FORMAT = "MM/DD/YYYY"

    You can change the session but on system..
    Saurabh Garg
    OCP 9i

Posting Permissions

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


Click Here to Expand Forum to Full Width