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

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

because you have the to_char parameters reversed. it should be

     select to_char(enddate,'DD-MON-YYYY') ...

>
> 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 ?
maybe it is NULL? (nope, i just tried that.) Was the data inserted via a pro*c application or similar? maybe the data is corrupt?

> 2. How can I display it in Oracle ?

I'm not sure.

> 3. I was given to understand that 31-DEC-9999 is the last date for
> datatype "DATE". How is the above even possible ?
see #1

> 4. Is this a bug I need to report ?

to who? ORACLE or your application development group? Try to eliminate the possibility you did something "odd" to the data before sending this off to ORACLE.

I'm not sure if this query gives the answer: SQL> select to_date('31-dec-9999', 'dd-mon-rr'),   2 to_date('31-dec-99', 'dd-mon-rr'),   3 to_char(to_date('31-dec-9999', 'dd-mon-rr'), 'dd-mon-yyyy'),   4 to_char(to_date('31-dec-99', 'dd-mon-rr'), 'dd-mon-yyyy')   5 from dual;

TO_DATE(' TO_DATE(' TO_CHAR(TO_ TO_CHAR(TO_ --------- --------- ----------- ----------- 31-DEC-99 31-DEC-99 31-dec-9999 31-dec-1999

SQL>
But I'm guessing somehow it might be treating your data as 1999. Seems to fit the example you posted.

   Ed Received on Wed May 25 2005 - 12:51:20 CDT