Re: Date fields before Year 2000.

On Thu, 16 Mar 2000 21:31:40 GMT, sumanp_at_my-deja.com wrote:

>Have you noticed that now (this year),
>the following statement does not work correctly.
>
>select * from table_name
>where date_column = '01-Dec-99';
>
>The above statement does not return
>any rows, even though data is present in
>the table. I can swear that the above
>statement returned rows last year.
>
>But the following statement returns rows.
>
>select * from table_name
>where date_column = to_date('01-Dec-1999', 'dd-mon-yyyy');
>
>Wierd, isn't it?
>I found the same behaviour in Oracle 7.3 and Oracle 8.
>
>Does anybody know why this is happening
>and if we can resolve it in some way.
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

This is not weird and this has all been well advertised by Oracle. Your NLS_DATE_FORMAT is DD-MON-YY.
This means that any element not present in the date mask will be derived from the sysdate.
In this case the century will be derived from the sysdate. So actually your query is
select *
from table_name
where date_column = '01-DEC-2099'.
To prevent this Oracle introduced (several years ago) the RR format. Using the date mask DD-MON-RR anything between 0 and 49 will be interpreted as 20.. and anything between 50 and 99 as 19.. Of course you are well advised to include a date mask in your statement ALWAYS, someone (or Oracle) may change the default date mask (as they did for the Netherlands when version 7 came) and you will endup in disaster.

Hth,

Sybrand Bakker, Oracle DBA Received on Thu Mar 16 2000 - 00:00:00 CST