The TO_CHAR function converts a DATETIME, number, or NTEXT expression to a TEXT expression in a specified format. This function is typically used to format output data.
TO_CHAR(datetime-exp, [datetime-fmt,] [option setting])
or
TO_CHAR(num-exp, [num-fmt,] [nlsparams])
or
TO_CHAR(ntext-exp)
A DATETIME expression to be converted to TEXT.
A text expression that identifies a datetime format template. This template specifies how the conversion from a DATETIME data type to TEXT should be performed. For information about datetime format templates, see Table 9-4, "Datetime Format Elements". The default value of datetime-fmt is controlled by the NLS_DATE_FORMAT option.
An OLAP option (such as NLS_DATE_LANGUAGE) and its new setting, which temporarily overrides the setting currently in effect for the session. Typically, this option identifies the language that you want datetime-exp to be translated into. See Example 8-138, "Displaying the Current Date and Time in Spanish".
Do not specify an option that set other options. For example, do not set NLS_DATE_LANGUAGE or NLS_TERRITORY; set NLS_DATE_LANGUAGE instead. While TO_CHAR saves and restores the current setting of the specified option so that it has a new value only for the duration of the statement, TO_CHAR cannot save and restore any side effects of changing that option. For example, NLS_TERRITORY controls the value of NLS_DATE_FORMAT, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_CALENDAR, and other options. When you change the value of NLS_TERRITORY in a call to TO_CHAR, all of these options are reset to their territory-appropriate default values twice: once when NLS_TERRITORY is set to its new value for the duration of the TO_CHAR statement, and again when the saved value of NLS_TERRITORY is restored.
A numeric expression to be converted to TEXT.
A text expression that identifies a number format model. This model specifies how the conversion from a numeric data type (NUMBER, INTEGER, SHORTINTEGER, LONGINTEGER, DECIMAL, SHORTDECIMAL) to TEXT should be performed.
The default number format model uses the decimal and thousands group markers identified by NLS_NUMERIC_CHARACTERS.
A text expression that specifies the thousands group marker, decimal marker, and currency symbols used in num-exp. This expression contains one or more of the following parameters, separated by commas:
NLS_CURRENCY symbol
NLS_ISO_CURRENCY territory
NLS_NUMERIC_CHARACTERS dg
A text expression that specifies the local currency symbol. It can be no more than 10 characters.
A text expression that identifies the territory whose ISO currency symbol is used.
A text expression composed of two different, single-byte characters for the decimal marker (d) and thousands group marker (g).
These parameters override the default values specified by the NLS_CURRENCY, NLS_ISO_CURRENCY, and NLS_NUMERIC_CHARACTERS options.
An NTEXT expression to be converted to TEXT. A conversion from NTEXT to TEXT can result in data loss when the NTEXT value cannot be represented in the database character set.
How TO_CHAR Handles Numerical Data Types
The TO_CHAR function converts INTEGER, SHORTINTEGER, LONGINTEGER, DECIMAL, and SHORTDECIMAL values to NUMBER before converting them to TEXT. Thus, TO_CHAR converts NUMBER values faster than other numeric data types.
Possible Effects of TO_CHAR Rounding
All number format models cause the number to be rounded to the specified number of significant digits. Table 8-17, "Possible Effects of Rounding" identifies some effects of rounding.
Table 8-17 Possible Effects of Rounding
| IF num-exp | THEN the return value |
|---|---|
|
has more significant digits to the left of the decimal place than are specified in the format, |
appears as pound signs ( |
|
is a very large positive value that cannot be represented in the specified format, |
is a tilde ( |
|
is a very small negative value that cannot be represented in the specified format, |
is a negative sign followed by a tilde ( |
Example 8-136 Converting a Date to CHAR
This statement converts today's date and specifies the format.
SHOW TO_CHAR(SYSDATE, 'Month DD, YYYY HH24:MI:SS')
The specified date format allows the time to be displayed along with the date.
November 30, 2000 10:01:29
Example 8-137 Converting a Numerical Value to Text
This statement converts a number to text and specifies a space as the decimal marker and a period as the thousands group marker.
SHOW TO_CHAR(1013.50, NA, NLS_NUMERIC_CHARACTERS ' .')
The value 1013.50 now appears like this:
1.013 50
Example 8-138 Displaying the Current Date and Time in Spanish
The following statements set the default language to Spanish and specify a new date format.
NLS_DATE_LANGUAGE = 'spanish' NLS_DATE_FORMAT = 'Day: Month dd, yyyy HH:MI:SS am'
The following statement displays the current date and time in Spanish.
SHOW TO_CHAR(SYSDATE) Viernes : Diciembre 01, 2000 08:21:17 AM
The NLS_DATE_LANGUAGE option changes the language for the duration of the statement. The following statement displays the date and time in German.
SHOW TO_CHAR(SYSDATE, NA, NLS_DATE_LANGUAGE 'german') Freitag : Dezember 01, 2000 08:26:00 AM