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, andTIMESTAMP WITH LOCAL TIME ZONEare 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 SECONDandINTERVAL YEAR TO MONTHdata 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 |
+-------------------------------------------------------------+