TO_CHAR

Return Value

TEXT

Syntax

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

or

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

or

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.

Examples

SHOW TO_CHAR(SYSDATE, 'Month DD, YYYY HH24:MI:SS')

November  30, 2000 10:01:29

SHOW TO_CHAR(1013.50, NA, NLS_NUMERIC_CHARACTERS ' .')

1.013 50

NLS_DATE_LANGUAGE = 'spanish'
NLS_DATE_FORMAT = 'Day: Month dd, yyyy HH:MI:SS am'

SHOW TO_CHAR(SYSDATE)
Viernes  : Diciembre  01, 2000 08:21:17 AM

SHOW TO_CHAR(SYSDATE, NA, NLS_DATE_LANGUAGE 'german')
Freitag   : Dezember  01, 2000 08:26:00 AM