TO_CHAR

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.

Return Value

TEXT

Syntax

TO_CHAR(datetime-exp, [datetime-fmt,] [option setting])

TO_CHAR(num-exp, [num-fmt,] [nlsparams])

TO_CHAR(ntext-exp)

Parameters

datetime-exp

A DATETIME expression to be converted to TEXT.

datetime-fmt

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.

option setting

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.

num-exp

A numeric expression to be converted to TEXT.

num-fmt

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.

See also:

"Number Format Models" in Oracle Database SQL Language Reference.

nlsparams

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

symbol

A text expression that specifies the local currency symbol. It can be no more than 10 characters.

territory

A text expression that identifies the territory whose ISO currency symbol is used.

dg

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.

ntext-exp

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.

Usage Notes

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 (`-~`).

Examples

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