Re: Y2K question

"Dave Morse" <dmorse_at_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_at_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_at_green1.demon.co.uk) Received on Sat Aug 08 1998 - 22:53:27 CDT