Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: TO_CHAR bug?
Thanks for all the feedback.
Dave, you are correct about "split and concat" - that works. I didnot
know about the
length restriction of dataformat. Not sure if that was causing it but
here is a little bit
more information.
If it was 24 char limit issue, then why does it work if the SQL statement looks as follows:
select TO_CHAR(systimestamp, 'YYYY-MM-DD"T"HH24:MI:SS.FF2"Z "') from dual;
Notice two space inside "Z " i.e, "Z<space><space>". This produces
correct result.
So apparently it is truncating last two characters which to me
indicates that it is
something to do with character set issue since TO_CHAR's behavior
depends
on db character set.
So looking at current character set of db, this is what I have:
CHARACTER SET UTF8
NATIONAL CHARACTER SET AL16UTF16
and the problem shows.
I recreate the db with the following character sets:
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET UTF8
and the problem is gone! woohooo!
So definitely a character set issue.
BTW, this is 9206 ORACLE.
Thanks
-- suvinay
Dave wrote:
> suvinay wrote:
> > Hi,
> > In a few databases, the following statements results in a missing "Z"
> > at the end?
> > A bug?
> >
> >
> > SQL> select TO_CHAR(systimestamp, 'YYYY-MM-DD"T"HH24:MI:SS.FF2"Z"')
> > from dual;
> >
> > TO_CHAR(SYSTIMESTAMP,'YYYY-MM-
> > ------------------------------
> > 2006-07-21T15:52:36.09
> >
> > //--- where is "Z" at the end?
> >
> > If I give two space in "Z{space}{space}" as "Z ", it works
> >
> > SQL> select TO_CHAR(systimestamp, 'YYYY-MM-DD"T"HH24:MI:SS.FF2"Z "')
> > from dual;
> >
> > TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD
> > --------------------------------
> > 2006-07-21T15:07:29.14Z
> >
> >
> > Help appreciated very much.
> >
> > Thanks.
> > -- Suvinay
> >
>
>