Purpose
TO_CHAR(datetime) converts a 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 specified format.
Syntax
TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ])
Parameters
Parameter |
Description |
|---|---|
| datetime | A 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. |
| fmt | The format of the return value. This parameter is optional. If you do not specify fmt, the value of datetime is converted to a value of the VARCHAR2 data type in the following format:
|
| nlsparam | The language in which month and day names are returned. This parameter is optional. |
Return type
The return type is VARCHAR2.
Examples
The following example uses the
TO_CHARfunction to return the current system date in theDS DLformat.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 setThe following example converts an interval value to a value in the specified format and specifies
AMERICANas 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