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

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 25 May 2005 02:18:21 -0700
Message-ID: <1117012701.076566.45890@g14g2000cwa.googlegroups.com>

Mike wrote:
> select dump(enddate) from pc_mike where enddate > '31-DEC-9999';
>
> DUMP(ENDDATE)
> ----------------------------------------
> Typ=12 Len=7: 200,100,1,1,1,1,1
> Typ=12 Len=7: 200,100,1,1,1,1,1
> Typ=12 Len=7: 200,100,1,1,1,1,1
> Typ=12 Len=7: 200,100,1,1,1,1,1
> Typ=12 Len=7: 200,100,1,1,1,1,1
> Typ=12 Len=7: 200,100,1,1,1,1,1
> Typ=12 Len=7: 200,100,1,1,1,1,1
> Typ=12 Len=7: 200,100,1,1,1,1,1
> Typ=12 Len=7: 200,100,1,1,1,1,1
> Typ=12 Len=7: 200,100,1,1,1,1,1
> Typ=12 Len=7: 200,100,1,1,1,1,1

Looks like you got corrupt date value stored in the table. How were these dates inserted in the first place? What happens if you add a day/month/year to these dates e.g. select enddate + 1 from ...; OR enddate + 365.

As others have pointed out... Oracle *does not allow* you to store date greater then 12-DEC-9999 in the date column. Also check with support at the metalink.

Enough said.

Regards
/Rauf Received on Wed May 25 2005 - 04:18:21 CDT