Oracle DB TO_CHAR、TO_DATE、TO_NUMBER 函数

• 使用TO_CHAR函数处理日期

TO_CHAR( date, 'format_model') 

格式样式具有以下特点：

• 必须放在单引号内

• 区分大小写

• 可以包含任何有效的日期格式元素

• 具有一个fm元素，用于删除填充的空格或隐藏前导零

• 与日期值之间用逗号分隔

TO_CHAR可以按照由format_model 指定的格式，将日期时间数据类型转换为数据类型为 VARCHAR2 的值。格式样式是一种字符文字，用于描述字符串中存储的日期时间的格式。

例如，字符串'11-Nov-1999' 的日期时间格式样式为'DD-Mon-YYYY' 。可以使用 TO_CHAR函数将日期从默认格式转换为指定的格式。

准则

• 格式样式是区分大小写的，而且必须放在单引号内。

• 格式样式可以包含任何有效的日期格式元素。但一定要使用逗号将日期值与格式样式 分隔开。

• 输出中的日和月名称会自动用空格填充。

• 要删除填充的空格或隐藏前导零，请使用填充模式fm元素。

hr@TEST0924> SELECT employee_id, TO_CHAR(hire_date, 'MM/YY') Month_Hired FROM  employees WHERE  last_name = 'Higgins'; 

EMPLOYEE_ID MONTH

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

        205 06/02

• 日期格式样式的元素

• 日期格式样式的元素

使用下表中列出的格式可显示时间信息和文字，并将数字更改为拼写数字。

• 使用TO_CHAR函数处理日期

hr@TEST0924> SELECT last_name,TO_CHAR(hire_date, 'fmDD Month YYYY') AS HIREDATE FROM   employees;

LAST_NAME                 HIREDATE

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

OConnell                  21 June 2007

Grant                     13 January 2008

Whalen                    17 September 2003

...

107 rows selected.

示例中的SQL 语句显示所有雇员的姓氏和聘用日期。聘用日期显示为“17 June 1987”。

示例：

修改幻灯片中的示例，使其以“Seventeenth of June 1987 12:00:00 AM ”格式显示日期。

hr@TEST0924> SELECT  last_name, TO_CHAR(hire_date, 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') HIREDATE FROM    employees; 

LAST_NAME                 HIREDATE

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

OConnell                  Twenty-First of June 2007 12:00:00 AM

Grant                     Thirteenth of January 2008 12:00:00 AM

Whalen                    Seventeenth of September 2003 12:00:00 AM

Hartstein                 Seventeenth of February 2004 12:00:00 AM

...

107 rows selected.

请注意，月份将遵循指定的格式样式；换句话说，首字母为大写，其它字母为小写。

• 使用TO_CHAR函数处理数字

TO_CHAR( number,  'format_model')

下面列出了一些格式元素，可以将其与TO_CHAR函数配合 使用，以便将数字值显示为字符：

如果将数字值作为字符串进行处理，应使用TO_CHAR函数将那些数字转换为字符串数据 类型，该函数会将NUMBER 数据类型的值转换为VARCHAR2 数据类型的值。此方法在进 行连接时尤其有用。

数字格式元素

如果要将数字转换为字符数据类型，则可以使用下列格式元素：

• 使用TO_CHAR函数处理数字

hr@TEST0924> SELECT TO_CHAR(salary, '$99,999.00') SALARY FROM employees WHERE  last_name = 'Ernst'; 

SALARY

-----------

  $6,000.00

• 如果数字的位数超过了格式样式中提供的位数，则Oracle Server  就会用由数字符号(#)  组成的字符串来替代整个数字。

• Oracle Server  会将存储的小数值舍入到格式样式中指定的小数位数。

• 使用TO_NUMBER 和TO_DATE函数

• 使用TO_NUMBER 函数可将字符串转换为数字格式：

TO_NUMBER( char[, 'format_model']) 

• 使用TO_DATE函数可将字符串转换为日期格式：

TO_DATE( char[, 'format_model']) 

• 这两个函数都有一个fx限定符。此限定符指定必须与 TO_DATE函数的字符参数和日期格式样式完全匹配。

你可能需要将 字符串转换为数字或日期。要完成此任务，请使用TO_NUMBER 或 TO_DATE函数。可根据前面演示的格式元素来选择格式样式。

fx限定符指定必须与TO_DATE函数的字符参数和日期格式样式完全匹配：

• 字符参数中的标点和引号中的文本必须与格式样式的相应部分完全匹配（大小写 除外）。

• 字符参数不能有额外的空格。如果不使用fx，Oracle Server  就会忽略额外的空格。

• 字符参数中的数字数据必须与格式样式中的相应元素具有相同的位数。如果不使用 fx，字符参数中的数字就会省略前导零。

示例：

显示在2007 年6  月21日进入公司的所有雇员的姓名和聘用日期。在以下示例中，月份June   后和数字21前有两个空格。因为使用了fx限定符，要求完全匹配，所以系统无法 识别单词May 之后的空格。

hr@TEST0924> SELECT last_name, hire_date FROM   employees WHERE  hire_date = TO_DATE('June  21,  2007', 'fxMonth DD, YYYY'); 

SELECT last_name, hire_date FROM   employees WHERE  hire_date = TO_DATE('  June  21,  2007', 'fxMonth DD, YYYY')

                                                                        *

ERROR at line 1:

ORA-01858: a non-numeric character was found where a numeric was expected

要看到输出，请删除“May”和“24”之间的额外空格来更正查询。

hr@TEST0924> SELECT last_name, hire_date FROM   employees WHERE  hire_date = TO_DATE('June 21, 2007', 'fxMonth DD, YYYY'); 

LAST_NAME                 HIRE_DATE

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

OConnell                  2007-06-21:00:00:00

Sullivan                  2007-06-21:00:00:00

• 将TO_CHAR和TO_DATE函数与RR日期格式结合使用

如果日期中的年份采用的格式为RR，并且只提供了最后2位年份，那么年份中的前两位数字就由两部分共同确定：提供年份的两位数字(指定年)，数据库服务器上当前日期中年份的后2位数字(当年)。确定指定年世纪的规则如下：

 

● 规则1 如果指定年在00~49之间，并且当前年份在00~49之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为15，而当前年份为2007，那么指定年就是2015。

 

● 规则2 如果指定年在50~99之间，并且当前年份在00~49之间，那么指定年的世纪就等于当前年份的世纪减去1。因此，指定年的前两位数字等于当前年份的前两位数字减去1。例如，如果指定年为75，而当前年份为2007，那么指定年就是1975。

 

● 规则3 如果指定年在00~49之间，并且当前年份在50~99之间，那么指定年的世纪就等于当前年份的世纪加上1。因此，指定年的前两位数字等于当前年份的前两位数字加上1。例如，如果指定年为15，而当前年份为2075，那么指定年就是2115。

 

● 规则4 如果指定年在50~99之间，并且当前年份在50~99之间，那么指定年的世纪就与当前年份的世纪相同。因此，指定年的前两位数字就等于当前年份的前两位数字。例如，如果指定年为55，而当前年份为2075，那么指定年就是2055。

 

如果日期中的年份使用RRRR格式，但是只提供了2位的年份，那么使用RR格式来解释年份。

下面看一个查询，它在解释15和75时使用了RR格式。下面这个查询假设当前年份为2013年：

sh@TEST0924>  SELECT TO_CHAR(TO_DATE('04-JUL-15', 'DD-MON-RR'), 'DD-MON-YYYY'),

  2  TO_CHAR(TO_DATE('04-JUL-75', 'DD-MON-RR'), 'DD-MON-YYYY')

  3  FROM dual;

TO_CHAR(TO_DATE('04- TO_CHAR(TO_DATE('04-

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

04-JUL-2015          04-JUL-1975

正如规则1和规则2解释的一样，15和75分别被解释为2015年和1975年。

 

下面这个查询假设当前年份为2075年：

sh@TEST0924> SELECT TO_CHAR(TO_DATE('04-JUL-15', 'DD-MON-RR'), 'DD-MON-YYYY'),

  2  TO_CHAR(TO_DATE('04-JUL-55', 'DD-MON-RR'), 'DD-MON-YYYY')

  3  FROM dual;

TO_CHAR(TO_DATE('04- TO_CHAR(TO_DATE('04-

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

04-JUL-2115          04-JUL-2055

正如规则3和规则4解释的一样，15和75分别被解释为2115年和2055年。

TO_CHAR和TO_DATE函数与RR

查找1990 年之前聘用的雇员时，可以使用RR格式。由于当前年份晚于1999，因此RR 格式将该日期的年份部分解释为在1950 到1999 年之间。

而如果使用下面的命令，则不会选中任何行，因为YY格式将该日期的年份部分解释为 当前世纪(2090) 。

SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-yyyy') FROM   employees WHERE  TO_DATE(hire_date, 'DD-Mon-yy') < '01-Jan-1990';