Re: Date & Time Format

Thomas kyte wrote:
>
> It is your use of the format modifier fm, from the sql language ref:
>
> FM ìFill modeî. This modifier suppresses blank padding in the return
> value of the TO_CHAR function:
>
>  In a date format element of a TO_CHAR function, this modifier
> suppresses blanks in subsequent character elements (such as
> MONTH) and suppresses leading and trailing zeroes for
> subsequent number elements (such as MI) in a date format
> model. Since there is no blank padding, the length of the return
> value may vary. Without FM, the result of a character element is
> always right padded with blanks to a fixed length and the
> leading zero are always returned for a number element.
>
>  In a number format element of a TO_CHAR function, this
> modifier suppresses blanks added to the left of the number in the
> result to rightñjustify it in the output buffer. Without FM, the
> result is always rightñjustified in the buffer, resulting in
> blankñpadding to the left of the number.
> ----------------------------------
>
> I think to get what you want, you will need to use:
>
>
> 1 SELECT
> 2 TO_CHAR(SYSDATE,'fmMonth ddth, YYYY, "at" ' ) ||
> 3 TO_CHAR(SYSDATE,'HH:MI P.M.') TODAY
> 4 FROM
> 5* DUAL
> SQL> /
>
> On Thu, 03 Apr 1997 09:16:50 -0500, Brad Skiles
> <bwskiles_at_adpc.purdue.edu> wrote:
>
> >Could someone please tell me what I'm doing wrong with the following?
> >
> >Here's the SQL:
> >
> >SELECT
> > TO_CHAR(SYSDATE,'fmMonth ddth, YYYY, "at" HH:MI P.M.') TODAY
> >FROM
> > DUAL;
> >
> >Here's the results:
> >
> >TODAY
> >----------------------------
> >April 3rd, 1997, at 9:8 A.M.
> >
> >
> >Notice the "9:8"? It should be "9:08". This only occurs when the the
> >first digit of the minutes is a zero (0). For example, if I run the
> >query 5 minutes later, it will correctly report the time as "9:13". I'm
> >rather befuddled, because I really think I'm doing it right.
> >
> >TIA.
> >
> >--brad Skiles, dba
> >Purdue University
> >bwskiles_at_adpc.purdue.edu
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Government
> Bethesda MD
>
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation

It might be helpful to note that, while its effects are as described by Thomas, the fm modifier affects all date elements to the right of its position in the mask **and before the next fm modifier (if any)**. Thus, assuming you want to retain blank suppression on the month, but to include leading zeros in the time, the format mask you would use is: 'fmMonth ddth, YYYY, "at" fmHH:MI P.M.'

 ^^                       ^^

Hope this helps.

Chrysalis.
' Received on Thu Apr 03 1997 - 00:00:00 CST