Oh and I forgot to add - this is 9206 Oracle on Solaris 64-bit.
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
>
>
>
>
> 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:46:54 CDT
