TO_CHAR (datetime)

Purpose

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

Syntax

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

Parameters

Return type

VARCHAR2

Examples

• Use the TO_CHAR function to return the current system date and convert the datetime value to 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
  

• Convert the interval value to the specified format and set the return language to AMERICAN.

  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