NLS_DATE_FORMAT - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: NLS_DATE_FORMAT

  1. #11
    Join Date
    Sep 2000
    Posts
    43
    When you talk about NLS_DATE_FORMAT, always keep oracle version in mind and check nls parameters because your default may not necessarily be YY for the year...
    Example, in 8i RR for the year is default which introduces it's limitations of 0-49 year belongs to 21th century but 50-99 year still belongs to the 20th century. I think you guys already know about this limitation :-)
    Connected to:
    Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
    With the Partitioning option
    JServer Release 8.1.6.0.0 - Production
    SQL> select value from nls_session_parameters where parameter='NLS_DATE_FORMAT';
    VALUE
    ----------------------------------------
    DD-MON-RR
    SQL> desc junk;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    COL1 DATE
    SQL> insert into junk values ('12-NOV-68');
    1 row created.
    SQL> alter session set nls_date_format='DD-MON-YYYY';
    Session altered.
    SQL> select * from junk;
    COL1
    -----------
    12-NOV-1968

    ..
    Connected to:
    Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
    With the Partitioning option
    JServer Release 8.1.6.0.0 - Production
    SQL> select value from nls_session_parameters where parameter='NLS_DATE_FORMAT';
    VALUE
    ----------------------------------------
    DD-MON-RR
    SQL> insert into junk values ('12-NOV-34');
    1 row created.
    SQL> alter session set nls_date_format='DD-MON-YYYY';
    Session altered.

    SQL> select * from junk;
    COL1
    -----------
    12-NOV-1968
    12-NOV-2034

    It's just a thing to keep in mind together with many other YYYY bugs :-)

    Alla S. Pfauntsch
    Oracle DBA
    ========================================
    "Life is what happens while you are planning something else".

  2. #12
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Iam aware of this.Thanks anyway...

  3. #13
    Join Date
    Jan 2001
    Posts
    2
    i want to change the date format
    i have changes the NLS_DATE_FOrmat parameter in initX.ora file and i restarted database but change didnot take place.
    how to set this ?
    thanks in advance

  4. #14
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Make sure. It depends on NLS_TERRITORY parm also. Did you bounce the database ? What format did you changed to ? Could you post the results you get for the following Query ?

    svrmgr> show parameter NLS_DATE_FORMAT



    [Edited by sreddy on 01-22-2001 at 09:45 AM]

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