Skip Headers
Oracle® OLAP DML Reference
11g Release 2 (11.2)

Part Number E17122-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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])

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. For more information, see "Number Format Models" in Oracle Database SQL Language Reference.

The default number format model uses the decimal and thousands group markers identified by NLS_NUMERIC_CHARACTERS.

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