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

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 24 May 2005 06:20:12 -0700
Message-ID: <1116940812.417877.233820@g49g2000cwa.googlegroups.com>

Mike wrote:
> 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 ?

What bug?

SQL> create table foo (d date);

Table created

SQL> insert into foo values (to_date('010110000','MMDDYYYY'));

insert into foo values (to_date('010110000','MMDDYYYY'))

ORA-01830: date format picture ends before converting entire input string

SQL> insert into foo values (to_date('01012000','MMDDYYYY'));

1 row inserted

SQL> select d from foo where trunc(d) >
trunc(to_date('12319999','MMDDYYYY'));

D

---

SQL> insert into foo values (to_date('12319999','MMDDYYYY'));

1 row inserted

SQL> select d from foo where trunc(d) >= trunc(to_date('12319999','MMDDYYYY'));

D

---

12/31/9999

Regards
/Rauf Received on Tue May 24 2005 - 08:20:12 CDT