| 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: Date & Time Format
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
 |
 |