Re: Have I found a new bug, and old bug or am I stupid?
Date: Fri, 21 Aug 1998 21:28:57 GMT
Message-ID: <35dde4a0.57000585_at_firewall>
You must put e to_date before the decode like this
insert into temp
(test_date)
values
(to_date(decode('20990101','0',null,to_date('20990101','YYYYMMDD'))))
/
Without the decode oracle will not able to detect a date field and then convert the date from a char format then you will loose then century (your date should be 01.01.0099)
You can test it with a create view like this :
1 create view aaa
2 as
3 select decode('20990101','0',null,to_date('20990101','YYYYMMDD'))
xxx
4* from dual
SQL> /
Vue créée.
SQL> desc aaa
Nom Non renseigné NULL? Type ------------------------------- -------- ---- XXX VARCHAR2(75)
1* select * from aaa
SQL> /
XXX
01/01/99
And now:
1 create view aaa
2 as
3 select
to_date(decode('20990101','0',null,to_date('20990101','YYYYMMDD')))
xxx
4* from dual
SQL> /
Vue créée.
SQL> desc aaa
Nom Non renseigné NULL? Type ------------------------------- -------- ---- XXX DATE
1* select to_char(xxx,'dd.mm.yyyy') from aaa SQL> / TO_CHAR(XXX,'DD.MM.YYYY')
01.01.1999
That's all folk's
On Tue, 18 Aug 1998 17:18:23 GMT, Martin Anderson <mhanders_at_bbn.com> wrote:
>
>--------------55639F3BA32987350AEA1D45
>Content-Type: text/plain; charset=us-ascii
>Content-Transfer-Encoding: 7bit
>
>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
>
>--------------55639F3BA32987350AEA1D45
>Content-Type: text/html; charset=us-ascii
>Content-Transfer-Encoding: 7bit
>
><HTML>
>RDBMS version 7.1.6.2.0
>
><P>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:<B></B>
>
><P><B>insert into temp</B>
><BR><B>(test_date)</B>
><BR><B>values</B>
><BR><B>(decode('20990101','0',null,to_date('20990101','YYYYMMDD')))</B>
><BR><B>/</B>
>
><P>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.
>
><P>Any help is ALWAYS recieved gratefully. The quicker the better
>because we are really up against the clock with this one.
>
><P>Thanks in advance.
>
><P>Martin Anderson</HTML>
>
>--------------55639F3BA32987350AEA1D45--
>
Received on Fri Aug 21 1998 - 23:28:57 CEST