Re: Possible Y2K trap in using DECODE?

In article <369a6064.2298099_at_news.siol.net>,   jmodic_at_src.si (Jurij Modic) wrote:

> >INSERT INTO destination_table (date_column)
> > SELECT to_date(
> > DECODE(char_date, '00000000', NULL, '01010001', NULL, char_date)
> > , 'DDMMYYYY')
> > FROM temp_table;
> >
> >I've had the default date format bite me, too, and have learned to avoid
> >relying on it. Hope this helps.
>
> Gerard,
> Your sollution is by far the easiest and the best for this case.
> Thanks!

Jurij,

as you say, this is a nice simple solution. However it is very difficult to spot implicit date conversions in code, and as long as you are using a default NLS_DATE_FORMAT that only has two digits for the year there *WILL* be problems.

In any non-trivial Oracle system, written by people with differing levels of experience and ability I would almost guarantee that there is some implicit date conversion going on. I have seen examples in PL/SQL, PRO*C, SQL scripts and even in view definitions. Since it is impossible to be sure that you have found and fixed every occurance, my preference is to use the 'RRRR' date format.

For new systems 'YYYY' is probably better, but it will 'break' any existing code that expects a 'YY' format.

For another example of the evils of implicit date conversions, you could also have a look at a previous posting of mine entitled "Warning: (mis)-use of TO_DATE instead of TRUNC" posted to this forum on 98/07/29 (available via the dejanews archive).

Regards
Yuri McPhedran

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Jan 13 1999 - 05:07:06 CST