-
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 :-)
-
Iam aware of this.Thanks anyway...
-
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
-
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]