Question on the date after '31-DEC-9999' in Oracle

From: Mike <michaelnazareth_at_gmail.com>
Date: 24 May 2005 04:52:48 -0700
Message-ID: <1116935568.121663.270500@g43g2000cwa.googlegroups.com>

SQL> desc pc_mike

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 ENDDATE                                            DATE

SQL> select enddate from pc_mike where enddate > '31-DEC-9999';

ENDDATE

---

01-JAN-00
01-JAN-00
01-JAN-00
01-JAN-00
01-JAN-00
01-JAN-00
01-JAN-00
01-JAN-00
01-JAN-00
01-JAN-00
01-JAN-00

SQL> select to_char('DD-MON-YYYY',enddate) from pc_mike where enddate > '31-DEC-9999';;
select to_char('DD-MON-YYYY',enddate) from pc_mike

               *
ERROR at line 1:
ORA-01722: invalid number

SQL> alter session set nls_date_format='DD-MON-YYYY';

Session altered.
SQL> select enddate from pc_mike where enddate > '31-DEC-9999';; ERROR:
ORA-01801: date format is too long for internal buffer

no rows selected
SQL> Questions:

1. Is the data in column "enddate" > 31-DEC-9999 i.e 01-JAN-10000 ?
2. How can I display it in Oracle ?
3. I was given to understand that 31-DEC-9999 is the last date for

datatype "DATE". How is the above even possible ? 4. Is this a bug I need to report ? Received on Tue May 24 2005 - 06:52:48 CDT