| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: to_char statement
In article <to2ltg1rf9g33f_at_beta-news.demon.nl>, "Sybrand says...
>
>
>"Nick Taylor" <ntwysiwyg_at_dataskill.co.uk> wrote in message
>news:3B8125D8.DDC9A930_at_dataskill.co.uk...
>> Hi
>>
>> This looks like a problem to me unless I'm missing something.
>>
>> In SQLPlus I'm trying the following:
>>
>> SQL> select to_char(sysdate,'ddTH fmMonth YYYY HH24:MI:SS') from dual;
>>
>> TO_CHAR(SYSDATE,'DDTHFMMONTH
>> ----------------------------
>> 20th August 2001 15:47:4
>>
>> SQL>
>>
>> The seconds lose the leading 0
>>
>> Whereas without the fm I get:
>>
>> SQL> select to_char(sysdate,'ddTH Month YYYY HH24:MI:SS') from dual;
>>
>> TO_CHAR(SYSDATE,'DDTHMONTHYY
>> ----------------------------
>> 20th August 2001 15:57:03
>>
>> SQL>
>>
>> With the seconds shown as 03 which is what I want.
>>
>> Oracle 8.1.6 on Linux
>>
>> Is this a bug or am I missing something?
>>
>> TIA
>>
>> Nick
>> --
>> Nick Taylor
>> Say No to Spammers - What You See Isn't What You Get to Email Me
>
>Looks like a bug.
>
No, thats what fm does!
<quote sql ref manual>
FM "Fill mode". This modifier suppresses blank padding in the return value of
the TO_CHAR function:
o In a date format element of a TO_CHAR function, this modifier suppresses blanks in subsequent character elements (such as MONTH)
and suppresses leading zeroes for subsequent number elements (such as MI) in a date format model.
Without FM, the result of a character element is always right padded
with blanks to a fixed length, and leading zeroes are always returned for a
number element. With FM, because there is no blank padding, the length of the
return value may vary.
</quote>
You can put FM in there more then once to toggle it on and off, perhaps what you want is:
SQL> select to_char(sysdate,'fmddTH Month YYYY fmHH24:MI:SS') from dual 2 /
TO_CHAR(SYSDATE,'FMDDTHMONTH
>Regards,
>
>Sybrand Bakker, Senior Oracle DBA
>
>
>
-- Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Mon Aug 20 2001 - 19:21:42 CDT
 |
 |