Re: Possible Y2K trap in using DECODE?

From: Grinberg L. <leon_at_grant.kharkov.ua>
Date: Thu, 14 Jan 1999 11:36:33 +0200
Message-ID: <77kdho$3hr@grant.grant.UUCP>

Jurij Modic wrote:
>>
>> SQL> INSERT INTO destination_table (date_column)
>> 2 SELECT DECODE('31.12.2000', '00000000', NULL,
>> 3 TO_DATE('31.12.2000','DD.MM.YYYY'))
>> 4 FROM DUAL;
>>
>> 1 row created.
>>
>> SQL> SELECT TO_CHAR(date_column,'DD.MM.YYYY') FROM destination_table;
>>
>> TO_CHAR(DATE_COLUMN,'DD.MM.YYYY')
>> ----------------------------------------------------------------------
>> 31.12.1900

I agree that it is unexpected but documented result.

But you can try

INSERT INTO destination_table (date_column)

SELECT DECODE('31.12.2000', '00000000', to_date(NULL), -- !!! TO_DATE('31.12.2000','DD.MM.YYYY'))
FROM DUAL; and you will get true datest.

Good luck,

Leon Grinberg leon_at_grant.kharkov.ua Received on Thu Jan 14 1999 - 03:36:33 CST