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

"suvinay" <suvinay (AT) gmail (DOT) com> a écrit dans le message de news: 1153512454.968662.250260 (AT) h48g20...oglegroups.com...
Works for me:

SQL> select TO_CHAR(systimestamp, 'YYYY-MM-DD"T"HH24:MI:SS.FF2"Z"')
2 from dual;
TO_CHAR(SYSTIMESTAMP,'YYYY-MM-
------------------------------
2006-07-22T07:14:45.10Z

But of course a bug is neither related to a version nor to an environment.

Regards
Michel Cadot

suvinay wrote:
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?

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:

Oh and I forgot to add - this is 9206 Oracle on Solaris 64-bit.


suvinay wrote:

suvinay wrote:

Yes it appears to be the Bug 4777057
WRONG OUTPUT TO_CHAR(DATE FORMAT) IN UTF8 DATABASE

I don't see any workaround listed in the bug description

Anurag

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: