oracle 时间格式化 to——datetime,oracle 日期格式化 TO_CHAR (datetime) 修饰语和后缀

Datetime Format Element Suffixes

Suffix

Meaning

Example Element

Example Value

TH

Ordinal Number

DDTH

4TH

SP

Spelled Number

DDSP

FOUR

SPTH or THSP

Spelled, ordinal number

DDSPTH

FOURTH

Notes on date format element suffixes:

When you add one of these suffixes to a datetime format element, the return value is always in English.

Datetime suffixes are valid only to format output. You cannot use them to insert a date into the database.

Format Model Modifiers

FM

Fill mode. Oracle uses blank characters to fill format elements to a constant width equal to the largest element for the relevant format model in the current session language. For example, when NLS_LANGUAGE is AMERICAN, the largest element for MONTH is SEPTEMBER, so all values of the MONTH format element are padded to 9 display characters. This modifier suppresses blank padding in the return value of the TO_CHAR function:

In a datetime 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, which suppresses blank padding, the length of the return value may vary.

In a number format element of a TO_CHAR function, this modifier suppresses blanks added to the left of the number, so that the result is left-justified 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.

FX

Format exact. This modifier specifies exact matching for the character argument and datetime format model of a TO_DATE function:

Punctuation and quoted text in the character argument must exactly match (except for case) the corresponding parts of the format model.

The character argument cannot have extra blanks. Without FX, Oracle ignores extra blanks.

Numeric data in the character argument must have the same number of digits as the corresponding element in the format model. Without FX, numbers in the character argument can omit leading zeroes.When FX is enabled, you can disable this check for leading zeroes by using the FM modifier as well.

If any portion of the character argument violates any of these conditions, then Oracle returns an error message.

Format Model Examples

SELECT TO_CHAR(SYSDATE, 'fmDDTH')||' of '||TO_CHAR

(SYSDATE, 'fmMonth')||', '||TO_CHAR(SYSDATE, 'YYYY') "Ides"

FROM DUAL;

Ides

------------------

3RD of April, 1998

SELECT TO_CHAR(SYSDATE, 'DDTH')||' of '||

TO_CHAR(SYSDATE, 'Month')||', '||

TO_CHAR(SYSDATE, 'YYYY') "Ides"

FROM DUAL;

Ides

-----------------------

03RD of April , 1998

SELECT TO_CHAR(SYSDATE, 'fmDay')||'''s Special' "Menu"

FROM DUAL;

Menu

-----------------

Tuesday's Special