Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: TO_CHAR bug?

Re: TO_CHAR bug?

From: suvinay <suvinay_at_gmail.com>
Date: 25 Jul 2006 08:45:25 -0700
Message-ID: <1153842325.591362.224050@s13g2000cwa.googlegroups.com>


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
> >

>

> Platform/version? What distinguishes databases that reproduce the
> problem from those that don't?
>

> Short answer: maybe. However, TFM states "The total length of a
> datetime format model cannot exceed 22 characters" and yours do, so does
> it work ok if you split it and concatenate the results?
Received on Tue Jul 25 2006 - 10:45:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US