DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: nls_date_format

  1. #1
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250
    I am having a very strange issue, i added the following line to my init.ora and restarted the database:
    NLS_DATE_FORMAT='MM/DD/YYYY'
    after this from the telnet unix window sqlplus i am getting the following:
    select sysdate from dual;
    SYSDATE
    ----------
    04/18/2002
    and
    select * from v$nls_parameters
    VALUE
    ----------------------------------------------------------------
    NLS_DATE_FORMAT
    MM/DD/YYYY

    but when I connect to the same database from sqlplus on my machine here is what i get:
    PARAMETER
    ----------------------------------------------------------------
    VALUE
    ----------------------------------------------------------------
    NLS_DATE_FORMAT
    DD-MON-RR
    select sysdate from dual;
    SYSDATE
    ---------
    18-APR-02

    why are these different on my local machine???
    but in the view v$parameter it shows the changed value:
    mm/dd/yyyy
    Please advise

    KN

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Exists 2 level of NLS in oracle.
    1. On server side, NLS may be setup in init.ora file (NLS_* parameters)
    2. on client side (!! for each client mashine seperatly)
    NLS parameters setup in:
    -- unix client -- environment variables
    -- winNT, Win9x -- in register (Oracle section)
    U should check NLS_DATE_FORMAT or NLS_LANG parameters on ur masine.

    If NLS_DATE_FORMAT don't setup then oracle impement default DATE_FORMAT from NLS_LANG (part territory)

  3. #3
    Join Date
    Mar 2001
    Posts
    144
    The date format is stord in the registry when the software was installed on your machine.

    It will usually be found in

    \\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ORACLE_HOME

    It may be that there will be 2 entries so look for that OR there may not be an entry in which case you will need to enter.

    As for the reason, I think it was because of the different format requirements needed for the clients as opposed to the server.


  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hmm rather than two levels I guess it's three

    database level
    instance level
    client side

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by pando
    hmm rather than two levels I guess it's three

    database level
    instance level
    client side
    Yes, sure, if we have OPS or RAC.
    In this case we have 3 levels for all NLS parameters, exept "semi-NLS" parameter --> characterset.
    -------------------------------------------
    I not reccomed setup different NLS parameters for different instance of OPS.
    It may create big problems with sort operations in parallel mode.

    [Edited by Shestakov on 04-18-2002 at 10:23 AM]

  6. #6
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250
    Thanx all of you for your replies, in registry i have only nls_lang set so that means the date_format is derived from the nls_lang value right?
    I changed the nls_date_format as users were getting ora 1801 date format is too long for internal buffer.. will the change in server (init.ora) solve this issue..
    KN

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    every country has some default nls parameter values so most of the times it is enough setting nls_lang to get the values or format you want

    so it is correct saying that your nls_date_format is derived from nls_lang

    if you change nls_date_format in your init.ora it wont solve the problem because at client side you have your nls settings the order looks like this

    if you set nls parameters at client the it rules otherwise instance setting rules if not set neither client and instance then database level rules

    client first
    instance second (init.ora)
    database third

    setting init.ora would work if you get rid of your nls_lang in client's registry

  8. #8
    Join Date
    Feb 2000
    Location
    NJ, United States
    Posts
    250
    Got it!
    Thanx guys..
    KN

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