Re: default date format in 8i

fumi wrote:
>
> cbantzer <christian_bantzer_at_psmfc.org> wrote in message news:387D0539.68E03C66_at_psmfc.org...
[...]
> > I am pretty sure the "where some_date > '1-dec-1999'" would have
> > produced an error on 7.3
> > Is this a documented "feature" of 8i?
> >
> > Btw. "where some_date > '1-dec-99'" works as expected too (i.e. expands
> > the 99 to 2099)
>
> Yes. It's a new feature in Oracle 8.0, not work in Oracle 7.x and before.
> Take a look at the Oracle8 SQL Reference manual.

OK, could you be a bit more specific, as to where in the Orcale 8 SQL Reference you saw this behavior documented, please? I have the 8i edition, and there it says on page 2-17 "Oracle automatically converts character values that are in the default date format into date values when they are used in date expressions."

and on page 2-35
"For example, a value that you insert into a DATE column must be a value of the DATE datatype or a charcter string in the default date format (Oracle implicitly converts character strings in the default date format to the DATE datatype). If the value is in another format, you must use the TO_DATE function to convert the value to the DATE datatype."

The latter to me clearly sounds like what my database is doing, (e.g. with NLS_DATE_FORMAT = 'DD-MON-YY' correctly processing where clauses like
where some_date = '12-dec-1999'
) is UNdocumented behavior.

The reason I am so anal about whether it's documented or not is that I have to decide between the following statement to users who write their own scripts

1. Be aware that Oracle takes 2-digit year dates to mean the 2-digit year in the current century (e.g. 12-dec-99 means 12-dec-2099). If you have written scripts which rely on the default date format (DD-MON-YY), and want those scripts to be able to work with dates from the 1900s, you will need to change your scripts to use explicit date formats.

OR

2) Be aware that Oracle takes 2-digit year dates to mean the 2-digit year in the current century (e.g. 12-dec-99 means 12-dec-2099). If you have written scripts which rely on the default date format (DD-MON-YY), you should change those scripts to use explicit character to date conversion. In the meantime character strings in either DD-MON-YY or DD-MON-YYYY formats will be implicitly converted to dates correctly.  

2) is what I observe, but I don't want to advise that, if it's based on an undocumented feature.

Thanks

   Christian Received on Tue Jan 18 2000 - 13:20:46 CST