All Products
Search
Document Center

TO_CHAR (datetime)

Last Updated: Jun 18, 2021

The TO_CHAR function converts a value of a data type to a value of the VARCHAR2 data type based on the format that is specified by the fmt parameter. The data types of which values can be converted include DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND, and INTERVAL YEAR TO MONTH. If you do not specify the fmt parameter, the value of the datetime parameter is converted to a value of the VARCHAR2 data type based on the following formats.

  • The values of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE are converted to the values that are in the default format of date and time values in the database. In the Data types topic, you can view the default format for each date and time type.

  • The values of the INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH data types are converted to the interval values in the numeric format.

Syntax

TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ])

Parameters

Parameter

Description

datetime

The values of the data types, such as DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND, and INTERVAL YEAR TO MONTH.

fmt

The output format parameter.

nlsparam

Controls the language of the returned month and day.

Return type

VARCHAR2 data type

Examples

Example 1: The following statement returns the current system date and converts the date and time value to the value in the DS DL format by using the TO_CHAR function.

SELECT TO_CHAR(SYSDATE,'DS DL') FROM DUAL;

The following query result is returned:

+-----------------------------------+
| TO_CHAR(SYSDATE,'DSDL')           |
+-----------------------------------+
| 03/08/2020 Sunday, March 08, 2020 |
+-----------------------------------+

Example 2: In the following statement, the interval value is converted to the value in the specified format and the language of the returned result is specified as AMERICAN.

SELECT TO_CHAR(interval'1' year, 'SS-MI-HH', 'nls_language = AMERICAN') FROM DUAL;

The following query result is returned:

+-------------------------------------------------------------+
| TO_CHAR(INTERVAL'1'YEAR,'SS-MI-HH','NLS_LANGUAGE=AMERICAN') |
+-------------------------------------------------------------+
| +01-00                                                      |
+-------------------------------------------------------------+