oracle 日期格式FM/FX和日期后缀SP/TH/SPTH/THSP

一、日期后缀

oracle的日期后缀，有SP、TH、SPTH/THSP几种
在oracle的官方联机文档上有，有如下描述：
http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF51083

摘录表格如下：

TH是序数方式显示,SP是用字母拼写方式显示.
THSP或SPTH，就是两种方式都有。

--普通方式显示,月份后面，有很大的空格。
SQL> SELECT TO_CHAR(SYSDATE, 'DD') || ' of '  ||
  2         TO_CHAR(SYSDATE, 'Month') || ', ' ||
  3         TO_CHAR(SYSDATE, 'YYYY') "Ides"
  4  FROM DUAL;

Ides
-------------------------------------------------
07 of May      , 2014

--序数方式显示,月份后面也一样有个大空格。
SQL> SELECT TO_CHAR(SYSDATE, 'DDTH') || ' of '||
  2         TO_CHAR(SYSDATE, 'Month')|| ', '  ||
  3         TO_CHAR(SYSDATE, 'YYYY') "Ides"
  4  FROM DUAL;

Ides
--------------------------------------------------
07TH of May      , 2014

--改变Dd的大小写，th的大小写也会变化
SQL> SELECT TO_CHAR(SYSDATE, 'DdTH') || ' of '||
  2         TO_CHAR(SYSDATE, 'Month')|| ', '  ||
  3         TO_CHAR(SYSDATE, 'YYYY') "Ides"
  4  FROM DUAL;

Ides
--------------------------------------------------
07th of May      , 2014

--改变dd的大小写，th的大小写也会变化
SQL> SELECT TO_CHAR(SYSDATE, 'ddTH')|| ' of '||
  2         TO_CHAR(SYSDATE, 'Month') || ', ' ||
  3         TO_CHAR(SYSDATE, 'YYYY') "Ides"
  4  FROM DUAL;

Ides
--------------------------------------------------
07th of May      , 2014

-- SP是字母拼写方式显示
SQL>  SELECT TO_CHAR(SYSDATE, 'DDsp') || ' of ' ||
  2          TO_CHAR(SYSDATE, 'Month') || ', ' ||
  3          TO_CHAR(SYSDATE, 'YYYY') "Ides"
  4   FROM DUAL;

Ides
----------------------------------------------------
SEVEN of May      , 2014

-- SPTH和THSP是字母拼写和序数方式显示
SQL> SELECT TO_CHAR(SYSDATE, 'DDSPTH') || ' of ' ||
  2         TO_CHAR(SYSDATE, 'Month') || ', ' ||
  3          TO_CHAR(SYSDATE, 'YYYY') "Ides"
  4  FROM DUAL;

Ides
---------------------------------------------------------
SEVENTH of May      , 2014

二、FM和FX格式

The FM and FX modifiers, used in format models in the TO_CHAR function, control blank padding and exact format checking.
FM和FX主要是用来控制填充和精确格式。

FM Fill mode. Oracle uses trailing blank characters and leading zeroes to fill format elements to a constant width. The width is equal to the display width of the largest element for the relevant format model:
    Numeric elements are padded with leading zeros to the width of the maximum value allowed for the element. For example, the YYYY element is padded to four digits (the length of '9999'), HH24 to two digits (the length of '23'), and DDD to three digits (the length of '366').
    The character elements MONTH, MON, DAY, and DY are padded with trailing blanks to the width of the longest full month name, the longest abbreviated month name, the longest full date name, or the longest abbreviated day name, respectively, among valid names determined by the values of NLS_DATE_LANGUAGE and NLS_CALENDAR parameters. For example, when NLS_DATE_LANGUAGE is AMERICAN and NLS_CALENDAR is GREGORIAN (the default), the largest element for MONTH is SEPTEMBER, so all values of the MONTH format element are padded to nine display characters. The values of the NLS_DATE_LANGUAGE and NLS_CALENDAR parameters are specified in the third argument to TO_CHAR and TO_* datetime functions or they are retrieved from the NLS environment of the current session.
    The character element RM is padded with trailing blanks to the length of 4, which is the length of 'viii'.
    Other character elements and spelled-out numbers (SP, SPTH, and THSP suffixes) are not padded.
The FM modifier suppresses the above padding in the return value of the TO_CHAR function.

fm是填充模式
如果是字符格式化，指定fm参数后将仅返回指定属性实际所占长度(不再以空格填充)。
如果是数值格式化，fm参数会自动舍弃被格式化元素的前置0。

--序数方式显示，并且fm方式显示,会把多余的前缀0和多余的空格去掉。
SQL> SELECT TO_CHAR(SYSDATE, 'fmDDTH') || ' of ' ||
  2         TO_CHAR(SYSDATE, 'fmMonth') || ', ' ||
  3         TO_CHAR(SYSDATE, 'YYYY') "Ides"
  4    FROM DUAL;

Ides
------------------------------------------------------------
7TH of May, 2014

--字母拼写方式显示，并且fm方式显示,会把多余的前缀0和多余的空格去掉。
SQL> SELECT TO_CHAR(SYSDATE, 'fmDDTHSP') || ' of ' ||
  2         TO_CHAR(SYSDATE, 'fmMonth') || ', ' ||
  3         TO_CHAR(SYSDATE, 'YYYY') "Ides"
  4  FROM DUAL;

Ides
-----------------------------------------------------
SEVENTH of May, 2014

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 zeros.
    When FX is enabled, you can disable this check for leading zeros by using the FM modifier as well.

fx是精确匹配模式，使用FX格式后:
1.字符及对应的格式必须严格一一对应，甚至连分隔符都要相符。
2.不允许有多余的空格。
3.数值参与格式需要完全对应(或通过fm参数去掉前置0)

SQL> select to_date('2014-5-10','yyyy-mm-dd') from dual;

TO_DATE('201
------------
10-MAY-14

SQL> select to_date('2014-5-10','yyyy/mm/dd') from dual;

TO_DATE('201
------------
10-MAY-14

SQL> select to_date('2014-5-10','fxyyyy-mm-dd') from dual;
select to_date('2014-5-10','fxyyyy-mm-dd') from dual
               *
ERROR at line 1:
ORA-01862: the numeric value does not match the length of the format item

SQL> select to_date('2014-5-10','fxyyyy/mm/dd') from dual;
select to_date('2014-5-10','fxyyyy/mm/dd') from dual
               *
ERROR at line 1:
ORA-01861: literal does not match format string

SQL> select to_date('2014-05-10','fxyyyy-mm-dd') from dual;

TO_DATE('201
------------
10-MAY-14

SQL> select to_date('2014-5-10','fxyyyy-fmmm-dd') from dual;
TO_DATE('201
------------
10-MAY-14