Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: TO_CHAR bug?
There you go - thanks Anurag.
I specially like their resolution:
<quote>
Workaround/s ~~~~~~~~~~~~ Do not use literal constants in date
format strings.
</quote>
Lovely!
Thanks again.
Anurag Varma wrote:
> suvinay wrote:
> > 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
> --snip--
>
>