TO_CHAR (datetime)

The TO_CHAR function converts a value 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 to a value of the VARCHAR2 data type in the format specified by the fmt parameter. If you do not specify the fmt parameter, this function converts the datetime parameter value to a value of the VARCHAR2 data type in the following format:

• The values of the DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE data types are converted to the default format of date and time values in the database. You can view the default format for values of each date and time data type in the "Data types" section.

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

Syntax

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

Parameters

Return type

The return type is VARCHAR2.

Examples

Example 1 : The following statement uses the TO_CHAR function to return the current system date and convert the date and time value to a result in the DS DL format:

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

The following result is returned:

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

Example 2 : The following statement converts an interval value to a result in a specified format and sets the return language to AMERICAN :

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

The following result is returned:

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