Oracle Function：TO

Oracle Function：TO_CHAR

Description

The Oracle/PLSQL TO_CHAR function converts a number or date to a string.将数字转换为日期或字符串

Syntax

TO_CHAR( value [, format_mask] [, nls_language] )

Parameters or Arguments

Value	A number or date that will be converted to a string.
format_mask	Optional. This is the format that will be used to convert value to a string.
nls_language	Optional. This is the nls language used to convert value to a string.

Please refer to "Format Models" for information on datetime formats.

TO_CHAR (character)

You can use this function in conjunction with any of the XML functions to generate a date in the database format rather than the XML Schema standard format.

TO_CHAR (character) converts	NCHAR, NVARCHAR2, CLOB, or NCLOB data to the database character set.
The value returned	is always VARCHAR2.
When you use this function to convert a character LOB into the database character set	if the LOB value to be converted is larger than the target type, then the database returns an error.

TO_CHAR (datetime)

TO_CHAR (datetime) converts

a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt.

If you omit fmt, then date is converted to a VARCHAR2 value as follows:

DATE values are converted to values in the default date format.

TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE values are converted to values in the default timestamp format.
TIMESTAMP WITH TIME ZONE values are converted to values in the default timestamp with time zone format.

The 'nlsparam' argument

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 'nlsparam', then this function uses the default date language for your session.

例子：

SQL> CREATE TABLE date_tab(

2 ts_col TIMESTAMP,

3 tsltz_col TIMESTAMP WITH LOCAL TIME ZONE,

4 tstz_col TIMESTAMP WITH TIME ZONE

5 );

Table created.

SQL> ALTER SESSION SET TIME_ZONE='-8:00';

Session altered.

SQL> INSERT INTO date_tab VALUES(

2 timestamp '1999-12-01 10:00:00',

3 TIMESTAMP '1999-12-01 10:00:00',

4 TIMESTAMP '1999-12-01 10:00:00'

5 );

1 row created.

SQL> INSERT INTO date_tab VALUES(

2 TIMESTAMP '1999-12-02 20:00:00-8:00',

3 TIMESTAMP '1999-12-02 20:00:00-8:00',

4 TIMESTAMP '1999-12-02 20:00:00-8:00'

5 );

1 row created.

SQL> SELECT * FROM date_tab;

TS_COL TSLTZ_COL TSTZ_COL

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

01-DEC-99 10.00.00.000000 AM 01-DEC-99 10.00.00.000000 AM 01-DEC-99 10.00.00.000000 AM -08:00

02-DEC-99 08.00.00.000000 PM 02-DEC-99 08.00.00.000000 PM 02-DEC-99 08.00.00.000000 PM -08:00

The example shows the results of applying TO_CHAR to different TIMESTAMP datatypes.

The result for a TIMESTAMP WITH LOCAL TIME ZONE column is sensitive to session time zone,

The results for the TIMESTAMP and TIMESTAMP WITH TIME ZONE columns are not sensitive to session time zone:

SQL> SELECT TO_CHAR(ts_col,'DD-MON-YYYY HH24:MI:SSxFF'),

2 TO_CHAR(tstz_col,'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM')

3 FROM date_tab;

TO_CHAR(TS_COL,'DD-MON-YYYYHH24:MI:SSXF TO_CHAR(TSTZ_COL,'DD-MON-YYYYHH24:MI:SSX FFTZH:

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

01-DEC-1999 10:00:00.000000 01-DEC-1999 10:00:00.000000 -08:00

02-DEC-1999 20:00:00.000000 02-DEC-1999 20:00:00.000000 -08:00

SELECT SESSIONTIMEZONE,
TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF')
FROM date_tab;

SESSIONTIMEZONE TO_CHAR(TSLTZ_COL,'DD-MON-YYYY
--------------- ------------------------------
-08:00 01-DEC-1999 10:00:00.000000
-08:00 02-DEC-1999 10:00:00.000000

ALTER SESSION SET TIME_ZONE = '-5:00';
SELECT TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF'),
TO_CHAR(tstz_col, 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM')
FROM date_tab;

TO_CHAR(TS_COL,'DD-MON-YYYYHH2 TO_CHAR(TSTZ_COL,'DD-MON-YYYYHH24:MI:
------------------------------ -------------------------------------
01-DEC-1999 10:00:00.000000 01-DEC-1999 10:00:00.000000 -08:00
02-DEC-1999 10:00:00.000000 02-DEC-1999 10:00:00.000000 -08:00

SELECT SESSIONTIMEZONE,
TO_CHAR(tsltz_col, 'DD-MON-YYYY HH24:MI:SSxFF')
FROM date_tab;

SESSIONTIMEZONE           TO_CHAR(TSLTZ_COL,'DD-MON-YYYY
------------------------- ------------------------------
-05:00                    01-DEC-1999 13:00:00.000000
-05:00                    02-DEC-1999 13:00:00.000000

TO_CHAR (number)

TO_CHAR (number) converts

n to a value of VARCHAR2 datatype, using the optional number format fmt.

The value n can be of type NUMBER, BINARY_FLOAT, or BINARY_DOUBLE.

If you omit fmt, then n is converted to a VARCHAR2 value exactly long enough to hold its significant digits.

The 'nlsparam' argument

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 = territory '

The characters d and g represent

the decimal character and group separator, respectively.

They must be different single-byte characters.

Within the quoted string, you must use two single quotation marks around the parameter values.

Ten characters are available for the currency symbol.

If you omit 'nlsparam' or any one of the parameters, then this function uses the default parameter values for your session.

Example

Numbers

TO_CHAR(1210.73, '9999.9')
Result: ' 1210.7'

TO_CHAR(-1210.73, '9999.9')
Result: '-1210.7'

TO_CHAR(1210.73, '9,999.99')
Result: ' 1,210.73'

TO_CHAR(1210.73, '$9,999.00')
Result: ' $1,210.73'

TO_CHAR(21, '000099')
Result: ' 000021'

Dates

TO_CHAR(sysdate, 'yyyy/mm/dd')
Result: '2003/07/09'

TO_CHAR(sysdate, 'Month DD, YYYY')
Result: 'July 09, 2003'

TO_CHAR(sysdate, 'FMMonth DD, YYYY')
Result: 'July 9, 2003'

TO_CHAR(sysdate, 'MON DDth, YYYY')
Result: 'JUL 09TH, 2003'

TO_CHAR(sysdate, 'FMMON DDth, YYYY')
Result: 'JUL 9TH, 2003'

TO_CHAR(sysdate, 'FMMon ddth, YYYY')
Result: 'Jul 9th, 2003'

"FM":This means that zeros and blanks are suppressed(压制).

The following is a list of valid parameters when the TO_CHAR function is used to convert a date to a string.

Parameter	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
YYY YY Y	Last 3, 2, or 1 digit(s) of year.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds.

Frequently Asked Questions

Question: Why doesn't this sort the days of the week in order?

Answer: In the above SQL, the fmDay format mask used in the TO_CHAR function will return the name of the Day and not the numeric value of the day.

To sort the days of the week in order, you need to return the numeric value of the day by using the fmD format mask as follows:

SELECT ename, hiredate, TO_CHAR((hiredate),'fmDay') "Day"
FROM emp
ORDER BY "Day";

SELECT ename, hiredate, TO_CHAR((hiredate),'fmD') "Day"
FROM emp
ORDER BY "Day";