Guess what? I'm stupid.

From: Martin Anderson <mhanders_at_bbn.com>
Date: Tue, 18 Aug 1998 20:26:07 GMT
Message-ID: <35D9E1C3.64886D61_at_bbn.com>

The answer is use to_date(null) to force the output datatype of the decode function to be date instead of varchar which is implied by using just null.

Martin

Martin Anderson wrote:

 RDBMS version 7.1.6.2.0

Has anyone found any problems with inserting into a DATE type column using a DECODE statement and a century other than the current century?  I have and it seems like a pretty major flaw.  For example try this:

insert into temp
(test_date)
values
(decode('20990101','0',null,to_date('20990101','YYYYMMDD')))
/

test_date is of type DATE.  I can't explain what is going on here.  I've tried different formats for the date with the same result, the wrong century is inserted into the table.  This could amount to a LOT of code changes for us.

Any help is ALWAYS recieved gratefully.  The quicker the better because we are really up against the clock with this one.

Thanks in advance.

Martin Anderson

  Received on Tue Aug 18 1998 - 22:26:07 CEST