SQL Reference - TO

TO_CHAR(number|date, [fmt],[nlsparam])

number|date: The value to be converted to a character data type. It can be a NUMBER or DATE data type.
fmt: An optional format model. A String that specifies how the number or date should be converted into a string. If fmt is NULL or a unique model format, then the value is converted to a VARCHAR2 data type.
nlsparam: An optional argument specifying the language in which month and day name replacements are to be performed. It has a VARCHAR2 data type and can have NLS_DATE_LANGUAGE or NLS_NUMERIC_CHARACTERS as parameters.

Example

SELECT TO_CHAR(sysdate, 'MM-DD-YYYY') AS formatted_date
FROM dual;

Output

FORMATTED_DATE
--------------
04-20-2023

Explanation

The TO_CHAR function is used in the SQL statement to convert a date or number type into a string. ‘sysdate’ fetches the current system date, and ‘MM-DD-YYYY’ specifies the format in which the date is displayed. The function is used as part of the SELECT statement to get the formatted date from ‘dual’, a system dummy table in Oracle.

TO_CHAR(value TIMESTAMP, format TEXT) RETURNS TEXT

value timestamp: This parameter refers to the actual timestamp value that needs to be formatted, which can include date and time.
format text: This parameter specifies the output format of the timestamp. This argument is a text string which contains various formatting codes that determine the final formatted output.

Example

SELECT TO_CHAR(12500, '99999');

Output

 "12500"

Explanation

In the example, TO_CHAR function is used to convert the integer 12500 to text based on the format specified. The ‘99999’ format represents a number with up to five digits.