Re: Oracle Y2K Question

From: Peter L <news_at_lenniep.freeserve.co.uk>
Date: Fri, 2 Apr 1999 18:05:28 +0100
Message-ID: <7e2v6i$png$1@news7.svr.pol.co.uk>

SQL*Plus assumes the current century if you use DD-MON-YY. I'm fairly sure this century is determined from the date on the machine and not the database's Sysdate. Your query would be looking for rows between 19-DEC-99 and
20-FEB-02 or 19-DEC-2099 and 20-FEB-2002 depending on the date on your

machine.  If you can't or don't want to use
to_date('19-DEC-1999','DD-MON-YYYY') in your query you can use
to_date('19-DEC-99','DD-MON-RR').  Using to date like this, on both sides of

the query should give you the right answer. The 'RR' format is not directly supported in Forms 3 so you can't use it in a format mask or put :date_a := to_date(to_char(:date_b),'DD-MON-RR') in your code to force the correct century. I seem to remember it lets you do it but you get very strange results. You need to do a select from dual like:

    select to_date(to_char(:date_a),'DD-MON-RR')     into :date_b from dual.
The above assumes date_a is a date field or variable, if it's char take the to_char out.

f.van.bortel_at_vnl.nl wrote in message <36FA09B6.D9A0DA3A_at_vnl.nl>...
>
>
>Mike Fellows wrote:
>
>> My question being when I run a SQL command to look for all records
>> greater than 31-DEC-99 any valid hit is returned correctly, if however
>> I try to look for data BETWEEN say 19-DEC-99 and 20-FEB-02 i dont get
>> the expected response.
>

>BTW not sure SQL supports the RR format - seem to recall that was Forms
>only
>
Received on Fri Apr 02 1999 - 11:05:28 CST