Oracle Database Discussions

I am using oracle10g version...

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for HPUX: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> create table test(start_date date,end_date date);

Table created.

SQL> insert into test values('01-JAN-00','31-DEC-99');

1 row created.

SQL> select to_char(start_date,'mm-dd-yyyy'),to_char(end_date, 'mm-dd-yyyy') from test;

TO_CHAR(ST TO_CHAR(EN
---------- ----------
01-01-2000 12-31-1999

SQL>

Issue 1 : In the above select statement, '31-DEC-99' is stored as 12-31-1999. It is supposed to store as 2099. Can any one please explain why it is stored like this?

When i select the records in the existing table,

SQL> select case_id, ret_id, start_date, to_char(start_date,'mm-dd-yyyy'),
2 end_date, to_char(end_date, 'mm-dd-yyyy')
3 from retenion
4 where ret_id = 141
5 /

CASE_ID RET_ID START_DAT TO_CHAR(ST END_DATE TO_CHAR(EN
---------- ------------ --------- ---------- --------- ----------
141 51 14-MAR-03 03-14-2003 30-JAN-00 00-00-0000
141 52 01-JAN-00 01-01-1900 31-DEC-99 12-31-9999


Issue 2 In the above output

First Record : Year 00 is showing as 0000. But it is supposed to show as 2000.

Second Record : In start date, Year 00 is showing 1900. But it supposed to show as 2000. In end date, year 99 showing as 9999. But is supposed to show as 2099. Any help is highly appreciated...

Edited by: Shrinika on Jan 25, 2010 9:55 AM

Edited by: Shrinika on Jan 25, 2010 9:56 AM