TO_CHAR (datetime)

Purpose

This function converts the values of the DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND, and INTERVAL YEAR TO MONTH data types into values of the VARCHAR2 data type in the specified format.

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 date and time 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