TO_CHAR (date conversion) SYNTAX: TO_CHAR(d [, fmt [, 'nlsparams'] ]) PURPOSE: Converts d of DATE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, d is converted to a VARCHAR2 value in the default date format. The 'nlsparams' specifies the language in which month and day names and abbreviations are returned. This argument can have this form: 'NLS_DATE_LANGUAGE = language' If you omit nlsparams, this function uses the default date language for your session. EXAMPLE: SELECT TO_CHAR(HIREDATE,'Month DD, YYYY') "New date format" FROM emp WHERE ename = 'SMITH' New date format ------------------------------- December 17, 1980 TO_CHAR (label conversion) SYNTAX: TO_CHAR(label [, fmt]) PURPOSE: Converts label of MLSLABEL datatype to a value of VARCHAR2 datatype, using the optional label format fmt. If you omit fmt, label is converted to a VARCHAR2 value in the default label format. TO_CHAR (number conversion) SYNTAX: TO_CHAR(n [, fmt [, 'nlsparams'] ]) PURPOSE: Converts n of NUMBER datatype to a value of VARCHAR2 datatype, using the optional number format fmt. If you omit fmt, n is converted to a VARCHAR2 value exactly long enough to hold its significant digits. The 'nlsparams' specifies these characters that are returned by number format elements: * decimal character * group separator * local currency symbol * international currency symbol This argument can have this form: 'NLS_NUMERIC_CHARACTERS = ''dg'' NLS_CURRENCY = ''text'' NLS_ISO_CURRENCY = ''text'' ' The characters d and g represent the decimal character and group separator, respectively. They must be different single-byte characters. Note that within the quoted string, you must use two single-quotes to represent one around the parameter values. If you omit 'nlsparams' or any one of the parameters, this function uses the default parameter values for your session. EXAMPLE: SELECT TO_CHAR(17145,'L099G999', 'NLS_NUMERIC_CHARACTERS = ''.,'' NLS_CURRENCY = ''AUD'' ') "Char" FROM DUAL Char -------------- AUD017,145
参数很多,最常用的是对日期 Select to_char(sysdate,’ss’) from dual取当前时间秒部分 Select to_char(sysdate,’mi’) from dual取当前时间分钟部分 Select to_char(sysdate,’HH24’) from dual取当前时间秒小时部分 Select to_char(sysdate,’DD’) from dual取当前时间日期部分 Select to_char(sysdate,’MM’) from dual取当前时间月部分 Select to_char(sysdate,’YYYY’) from dual取当前时间年部分 Select to_char(sysdate,’w’) from dual取当前时间是一个月中的第几周(从1日开始算) Select to_char(sysdate,’ww’) from dual取当前时间是一年中的第几周(从1.1开始算) Select to_char(sysdate,’iw’) from dual取当前时间是一年中的第几周(按实际日历的) Select to_char(sysdate,’d’) from dual取当前时间是一周的第几天,从星期天开始,周六结束 Select to_char(sysdate,'day') from dual 取当前日是星期几,和数据库设置的字符集有关,会输出’Tuesday’ Select to_char(sysdate,'ddd') from dual 当前日是一年中的第几天 还有格式化数字google
格式种类就多了。
format specified by the date format fmt. If you omit fmt, d is
converted to a VARCHAR2 value in the default date format. The 'nlsparams' specifies the language in which month and day names
and abbreviations are returned. This argument can have this form: 'NLS_DATE_LANGUAGE = language' If you omit nlsparams, this function uses the default date language
for your session. EXAMPLE: SELECT TO_CHAR(HIREDATE,'Month DD, YYYY')
"New date format"
FROM emp
WHERE ename = 'SMITH' New date format
-------------------------------
December 17, 1980 TO_CHAR (label conversion) SYNTAX: TO_CHAR(label [, fmt]) PURPOSE: Converts label of MLSLABEL datatype to a value of VARCHAR2 datatype,
using the optional label format fmt. If you omit fmt, label is
converted to a VARCHAR2 value in the default label format.
TO_CHAR (number conversion)
SYNTAX: TO_CHAR(n [, fmt [, 'nlsparams'] ]) PURPOSE: Converts n of NUMBER datatype to a value of VARCHAR2 datatype, using
the optional number format fmt. If you omit fmt, n is converted to
a VARCHAR2 value exactly long enough to hold its significant digits. The 'nlsparams' specifies these characters that are returned by
number format elements: * decimal character
* group separator
* local currency symbol
* international currency symbol This argument can have this form: 'NLS_NUMERIC_CHARACTERS = ''dg''
NLS_CURRENCY = ''text''
NLS_ISO_CURRENCY = ''text'' ' The characters d and g represent the decimal character and group
separator, respectively. They must be different single-byte characters. Note that within the quoted string, you must use two
single-quotes to represent one around the parameter values. If you omit 'nlsparams' or any one of the parameters, this function
uses the default parameter values for your session. EXAMPLE: SELECT TO_CHAR(17145,'L099G999',
'NLS_NUMERIC_CHARACTERS = ''.,''
NLS_CURRENCY = ''AUD'' ') "Char" FROM DUAL Char
--------------
AUD017,145
Select to_char(sysdate,’ss’) from dual取当前时间秒部分
Select to_char(sysdate,’mi’) from dual取当前时间分钟部分
Select to_char(sysdate,’HH24’) from dual取当前时间秒小时部分
Select to_char(sysdate,’DD’) from dual取当前时间日期部分
Select to_char(sysdate,’MM’) from dual取当前时间月部分
Select to_char(sysdate,’YYYY’) from dual取当前时间年部分
Select to_char(sysdate,’w’) from dual取当前时间是一个月中的第几周(从1日开始算)
Select to_char(sysdate,’ww’) from dual取当前时间是一年中的第几周(从1.1开始算)
Select to_char(sysdate,’iw’) from dual取当前时间是一年中的第几周(按实际日历的)
Select to_char(sysdate,’d’) from dual取当前时间是一周的第几天,从星期天开始,周六结束
Select to_char(sysdate,'day') from dual 取当前日是星期几,和数据库设置的字符集有关,会输出’Tuesday’
Select to_char(sysdate,'ddd') from dual 当前日是一年中的第几天
还有格式化数字google
to_char(1210.73, '9999.9') would return '1210.7'
to_char(1210.73, '9,999.99') would return '1,210.73'
to_char(1210.73, '$9,999.00') would return '$1,210.73'
to_char(21, '000099') would return '000021' to_char(sysdate, 'yyyy/mm/dd');
to_char(sysdate, 'Month DD, YYYY');
to_char(sysdate, 'FMMonth DD, YYYY');
to_char(sysdate, 'MON DDth, YYYY');
to_char(sysdate, 'FMMON DDth, YYYY');
to_char(sysdate, 'FMMon ddth, YYYY');
SQL> SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY') "MY_DATE" FROM DUAL;MY_DATE
------------
18-9月 -200SQL> SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY','NLS_DATE_LANGUAGE=AMERICAN') "MY_DATE" FROM DUAL;MY_DATE
-----------
18-SEP-2008SQL>