Hi All,
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Just went through the manuals. Have worked a lot on dates but this century thing is really confusing .Let me summarize what i know and then i will explain the scenario . Nls session settings determines how my date string will be treated while insertions and while retrieval from the database.Changing the nls settings from the client end implicitly cascades the change to the server session . YYYY format doesnt consider the century .RR treats any value from 0-49 ,to be in the current century and 50-99 in the previous century .
step by step of What i did
{code}
SQL> create table datee
2 (
3 a date
4 )
SQL> select * from nls_session_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
SQL> insert into datee values ('12-jul-88');
1 row created
SQL> insert into datee values ('12-jul-48');
SQL> select * from datee;
A
---------
12-JUL-88
12-JUL-48
SQL> alter session set nls_date_format='dd-mon-yyyy';
Session altered.
SQL> select * from datee;
A
-----------
12-jul-1988
12-jul-2048
SQL> alter session set nls_date_format='dd-mon-rrrr';
Session altered.
SQL>
SQL> select * from datee;
A
-----------
12-jul-1988
12-jul-2048
SQL>
SQL>
SQL>
SQL>
{code}
here my default nls_session_format is 'DD-MON-RR' . When i use insert into datee values ('12-JUL-88') --Does oracle stores just 88 into the database ? or does it stores '1988'? I assume that it stores 1988 (seeing the nls_date_format as 'DD-MON-RR') it would find out the current century and the previous century and insert based on the 0-49 & 50-99 logic.
Once my date gets stored in the database in a 4 digit format .I am comfortable with the reterival logics.
One more scenario as shown below
{code}
SQL> create table da(a date );
Table created.
SQL>
SQL> alter session set nls_date_format='DD-MON-YY';
Session altered.
SQL> insert into da values ('12-JUL-13');
1 row created.
SQL> insert into da values ('12-JUL-83');
1 row created.
SQL>
SQL>
SQL> select * from da;
A
---------
12-JUL-13
12-JUL-83
SQL> alter session set nls_date_format='DD-MM-RRRR';
Session altered.
SQL> select * from da;
A
----------
12-07-2013
12-07-2083
{code}
What would be the values at the time of actual insertion into the database? oracle would have determined the nls_session_parameter to be 'DD-MON-YYYY' and would have inserted '12-JUL-0083' and '12-JUL-0013'
Why did oracle return 2083 .It should have gone by the 0-49 and 50-99 logic? since I altered the session using "alter session set nls_date_format='DD-MM-RRRR'' .And as per that logic ,we should have got '12-07-1983'
Thanks ...