Re: Possible Y2K trap in using DECODE?

From: Gerard M. Averill <e-mail.address_at_my.sig>
Date: Mon, 11 Jan 99 17:07:47 GMT
Message-ID: <77dauq$fik$1@news.doit.wisc.edu>

In article <3699203e.42677404_at_news.siol.net>, jmodic_at_src.si (Jurij Modic) wrote:

snip

>The only workaround for this problem I could found in the meantime was
>to force the first result in the DECODE to be non-null date value and
>to make sure this first condition never evaluates to TRUE. In my case
>for example I know that in a source file all date values can contain
>only numerical characters, so I can use the following DECODE to be
>sure the resulted datatype will be of type DATE:
>
>INSERT INTO destination_table (date_column)
> SELECT DECODE(char_date, 'xyzabc_-', SYSDATE,
> '00000000', NULL,
> '01010001', NULL,
> TO_DATE(char_date,'DDMMYYYY'))
> FROM temp_table;
>

Like you said earlier, your problem comes from the implicit converts from date to varchar (and vice versa) using the default date format. You could change the default date format, but since it can be changed by each user and thus override your system setting this wouldn't guarantee a solution (though it could be acceptable). The other solution, which doesn't rely on a "magic" value such as 'xyzab_-', is to make sure there are no implicit conversions:

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

---

Gerard M. Averill, Researcher
CHSRA, University of Wisconsin - Madison GAverill<at>chsra<dot>wisc<dot>edu Received on Mon Jan 11 1999 - 11:07:47 CST