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

From: Tony Andrews <andrewst_at_onetel.com>
Date: 24 May 2005 05:41:45 -0700
Message-ID: <1116938505.220630.204540@g44g2000cwa.googlegroups.com>

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

You should have typed:

SQL> select to_char(enddate,'DD-MON-YYYY') from pc_mike where enddate > '31-DEC-9999'; i.e. date then format, not format then date

Also that SQL makes assumptions about the implicit date format mask, so would be better as:

SQL> select to_char(enddate,'DD-MON-YYYY') from pc_mike where enddate > to_date('31-DEC-9999','DD-MON-YYYY'); Received on Tue May 24 2005 - 07:41:45 CDT