TO_CHAR(datetime)

Purpose

TO_CHAR(datetime) converts a datetime or interval value of the DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND, or INTERVAL YEAR TO MONTH data type to a value of the VARCHAR2 data type in the format specified by the fmt parameter.

Syntax

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

Parameters

Return type

The return type is VARCHAR2.

Examples

• The following example uses the TO_CHAR() function to return the current system date in the DS DL format.

  obclient> SELECT TO_CHAR(SYSDATE,'yyyy/mm/dd'),TO_CHAR(SYSDATE,'DS DL') FROM DUAL;
  +-------------------------------+----------------------------------------+
  | TO_CHAR(SYSDATE,'YYYY/MM/DD') | TO_CHAR(SYSDATE,'DSDL')                |
  +-------------------------------+----------------------------------------+
  | 2021/11/18                    | 11/18/2021 Thursday, November 18, 2021 |
  +-------------------------------+----------------------------------------+
  1 row in set
  

• The following example converts an interval value to a value in the specified format and specifies AMERICAN as the language in which the value is returned.

  obclient> SELECT TO_CHAR(interval'1' year, 'SS-MI-HH', 'nls_language = AMERICAN') FROM DUAL;
  +-------------------------------------------------------------+
  | TO_CHAR(INTERVAL'1'YEAR,'SS-MI-HH','NLS_LANGUAGE=AMERICAN') |
  +-------------------------------------------------------------+
  | +01-00                                                      |
  +-------------------------------------------------------------+
  1 row in set