--不用补0的加fxfm select to_char(sysdate,'yyyy-FXFMmm-FXFMDD') from dual
学习了 SQL> select to_char(sysdate,'yyyy-FXFMmm-FXFMDD') from dual;TO_CHAR(SY ---------- 2011-2-22
select to_char(to_date('20100102','yyyymmdd'),'yyyy-FXFMmm-FXDD') from dual
FM Returns a value with no leading or trailing blanks. FX Requires exact matching between the character data and the format model.
The FM and FX modifiers, used in format models in the TO_CHAR function, control blank padding and exact format checking.A modifier can appear in a format model more than once. In such a case, each subsequent occurrence toggles the effects of the modifier. Its effects are enabled for the portion of the model following its first occurrence, and then disabled for the portion following its second, and then reenabled for the portion following its third, and so on.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.
select to_char(sysdate,'yyyy-FXFMmm-FXFMDD') from dual
学习了
SQL> select to_char(sysdate,'yyyy-FXFMmm-FXFMDD') from dual;TO_CHAR(SY
----------
2011-2-22
select to_char(to_date('20100102','yyyymmdd'),'yyyy-FXFMmm-FXDD') from dual
FM Returns a value with no leading or trailing blanks.
FX Requires exact matching between the character data and the format model.
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.