Re: to_char statement

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 20 Aug 2001 11:15:36 -0700
Message-ID: <9lrk480mau@drn.newsguy.com>

In article <3B8125D8.DDC9A930_at_dataskill.co.uk>, Nick says...
>
>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
>

you told us to do that, 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

20th August 2001 14:13:02

>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

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

Received on Mon Aug 20 2001 - 13:15:36 CDT