Dates and Times

Fill Mode Formatting with the FM Modifier

Fill Mode

The Fill Mode modifier, FM, toggles the suppression of blank padding in character format elements like "MONTH" and leading zeros in numeric format elements like "YYYY". Here are some examples.

column without_fm format a15
column with_fm    format a15

select
  c_date as input,
  '"' || to_char( c_date, 'MONTH'   ) || '"' as without_fm ,
  '"' || to_char( c_date, 'FMMONTH' ) || '"' as with_fm
from t ;
 
INPUT       WITHOUT_FM      WITH_FM
----------- --------------- ---------------
-2400-10-31 "OCTOBER  "     "OCTOBER"
-0500-02-28 "FEBRUARY "     "FEBRUARY"
-0001-12-31 "DECEMBER "     "DECEMBER"
 0000-01-01 "000000000"     "000000000"
 0001-06-21 "JUNE     "     "JUNE"
 0820-11-23 "NOVEMBER "     "NOVEMBER"
 1947-12-16 "DECEMBER "     "DECEMBER"
 2007-06-15 "JUNE     "     "JUNE"
 
select
  c_date as input,
  to_char( c_date, 'YYYY' ) as without_fm ,
  to_char( c_date, 'FMYYYY' ) as with_fm
from t ;
 
INPUT       WITHOUT_FM      WITH_FM
----------- --------------- ---------------
-2400-10-31 2400            2400
-0500-02-28 0500            500
-0001-12-31 0001            1
 0000-01-01 0000            0000
 0001-06-21 0001            1
 0820-11-23 0820            820
 1947-12-16 1947            1947
 2007-06-15 2007            2007
 

FM will not, however, suppress blanks in number elements.

select
  c_date ,
  '"' || to_char( c_date, 'SYYYY'   ) || '"' as without_fm ,
  '"' || to_char( c_date, 'FMSYYYY' ) || '"' as with_fm
from t ;
 
C_DATE      WITHOUT_FM      WITH_FM
----------- --------------- ---------------
-2400-10-31 "-2400"         "-2400"
-0500-02-28 "-0500"         "-500"
-0001-12-31 "-0001"         "-1"
 0000-01-01 "00000"         "00000"
 0001-06-21 " 0001"         " 1"
 0820-11-23 " 0820"         " 820"
 1947-12-16 " 1947"         " 1947"
 2007-06-15 " 2007"         " 2007"
 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12346.html]SQL Snippets: Dates and Times - Fill Mode Formatting with the FM Modifier[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12346.html">SQL Snippets: Dates and Times - Fill Mode Formatting with the FM Modifier</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Dates and Times - Fill Mode Formatting with the FM Modifier
  • URL (href): http://www.sqlsnippets.com/en/topic-12346.html