Xref: alice comp.databases.oracle.misc:18406
Path: alice!news-feed.fnsi.net!news2.ais.net!jamie!ais.net!news.idt.net!woodstock.news.demon.net!demon!news.demon.co.uk!demon!green1.demon.co.uk!not-for-mail
From: david@green1.demon.co.uk (David Greensmith)
Newsgroups: comp.databases.oracle.misc
Subject: Re: Y2K question
Date: Sun, 09 Aug 1998 03:53:27 GMT
Message-ID: <35cd1c0b.20874136@news.demon.co.uk>
References: <6q80um$fq3$1@news.iquest.net>
X-Trace: news.demon.co.uk 902635081 nnrp-06:20666 NO-IDENT green1.demon.co.uk:158.152.173.64
X-Complaints-To: abuse@demon.net
X-Newsreader: Forte Free Agent 1.11/32.235
Lines: 35

"Dave Morse" <dmorse@nospam.cinergy.com> wrote:

>I recently read a quote in a publication that said:
>
>"And then, of course, there is the dreaded Year 2000 problem in Oracle.  If
>your code contains lines like this:  v_mydate := TO_DATE(v_mystr,
>'mm/dd/yy');  -- then you have some big problems coming down the pike."
>
>Do you agree?  The TO_DATE function converts strings to the default system
>date.  In the above example, the 'mm/dd/yy' is simply telling Oracle what
>format the string 'v_mystr' is in, not what to convert it to.
>
>--
>
>Dave
>dmorse@cinergy.com  (** please remove the 'nospam' from the address **)
>
>
Allowing Oracle to perform implicit datatype conversions is at the
root of most of these sorts of problems. 
Performing to_date(im_a_date, 'DD-MON-YYYY') where im_a_date is of
type DATE causes Oracle to first convert this into a string (what
to_date expects as its first parameter).  This will use whatever
NLS_DATE_FORMAT is for your session - beware, this is not necessarily
the setting in the v$parameter table, if you are connecting from a PC
client it quite often defaults to DD-MON-YY.  We found that the
flexibility of Oracle can be a curse:
to_date('10-MAY-98','DD-MON-YYYY') does not give the expected error
(as it does if the conversion picture is too short) but will actually
assume you mean the year 98 (i.e. a first century date).  To force an
error, use the FXYYYY format.

David
David Greensmith :-)
(david@green1.demon.co.uk)