TO_CHAR(datetime)

Description

This function is used to convert a date and time value to a VARCHAR type.

Syntax declarations

TO_CHAR(datetime [, fmt])

Parameter description

• datetime: specifies the datetime data to convert.
• fmt: An optional parameter that specifies the output format. If you do not specify the fmt parameter, the value is converted to the VARCHAR data type based on the format in the datetime parameter. For more information, see fmt.

fmt

fmt parameter values are as follows:

Format	Description
-、/、,、.、;、:、"text"	specifies a delimiter. In addition to the standard delimiters, you can also use text as a delimiter. For example, in TO_CHAR(SYSDATE(), 'YYYY "year" mm "month" dd "day"').
AD/A.D.	Abbreviation for Anno Domini. In some NLS settings, it is converted into AD.
AM/A.M.	Indicates AM. It will be returned with AM or PM.
BC/B.C.	BCE.
CC/SCC	Returns the century in digits. If the last two digits of the year are from 01 to 99, it returns the first two digits + 1; otherwise, it returns the first two digits.
D	Specifies the day of the week as a number from 1 to 7.
DAY	Returns the day of the week for a date. For example, when you specify NLS_DATE_LANGUAGE = AMERICAN, this function returns the day of the week in English, such as Monday.:::info The format of the return value depends on the value of the NLS_DATE_LANGUAGE parameter. For example, when you specify NLS_DATE_LANGUAGE = SIMPLIFIED CHINESE, this function returns the day of the week in Chinese, such as Monday.:::
DD	Returns the day of the month as a number from 1 to 31.
DDD	Returns the day of the year, from 1 to 366.
DL	Returns the date in the long date format. It is controlled by the NLS_TERRITORY and NLS_LANGUAGE parameters.
DS	Returns the short date format. It is controlled by the NLS_TERRITORY and NLS_LANGUAGE parameters.
E	Denotes the era. The abbreviations are suitable for only the following calendars: Japanese, Chinese, and Thai.
FF [1..9]	Represents milliseconds. If the number of digits is not specified, the default precision is used. This data type is only supported for the TIMESTAMP type.
FM	Returns no result.
HH/HH12	Displays the hour in a 12-hour clock (1 through 12).
HH24	Indicates the hour in a 24-hour format (0 to 23).
IW	The ISO week number (1 to 52 or 1 to 53).
IYYY, IYY, IY, I	Returns the year part of the ISO date, with 4, 3, 2, or 1 digit, respectively.
J	Indicates a Julian day (a calendar system often used in astronomy), calculated as an integer based on the date in the Gregorian calendar from 1 January 4712 BCE, using the formula (Gregorian date + 4712) × average number of days in a Julian year.
MI	Returns the minutes.
MM	Returns the two-digit month (01 to 12).
MON	Returns the abbreviation for the month, which depends on the NLS_DATE_LANGUAGE value. For example, 04 is displayed as 4 in a Chinese environment.
MONTH	Returns the name of the month, based on the value of the NLS_DATE_LANGUAGE parameter. In the context of this parameter, the names of the months in English are different from those in other languages. For example, in Simplified Chinese, the name of April is April, and the name of April 4 is "4 4".
PM/P.M.	Indicates afternoon. The AM/A.M. or PM/P.M. identifier is returned to indicate am or pm.
Q	Returns the quarter (1 to 4).
RR	Indicates the last two digits of a year.
SS	Returns the second (0 to 59) of the minute.
SSSSS	Indicates the number of seconds elapsed since midnight, from 0 to 86399.
SYYYY	Four-digit year with the S prefix for BC years.
TZD	Time zone and daylight saving time information, expressed in the short format of the time zone in combination with the daylight saving time information. It must match the settings of the format TZR. It includes three types of parameters, namely, TZH, TZM, and TZR, which are related to the time zone but cannot be directly used in the TO_CHAR function.
TZH	The hour in the time zone, for example, hh:mi:ss.fftzh:tzm.
TZM	Minutes in the time zone.
TZR	The region in the time zone, which must be a supported time zone by the database. For example, it can be US/Pacific.
WW	Indicates the number of the week within the year, where the first week begins on January 1st and ends on the last Sunday of the year (1 to 53). For example, the weeks from 2008-01-01 to 2008-01-07 are numbered 1, and those from 2008-01-09 to 2008-01-13 are numbered 2.
W	A number representing the weekday.
X	It is a root symbol and has no particular utility. You can use it only with the TIMESTAMP type.
Y,YYY	Returns a four-digit year, separated by commas. For example, 2,008.
YYYY、YYY、YY、Y	The last 4, 3, 2, or 1 digits of the year. For example, 2008 can be represented as 2008, 008, 08, or 8.

Return type

• Returns data of the VARCHAR type.
• If the datetime value is NULL, the value NULL is returned.

Example

SELECT
    TO_CHAR(SYSDATE()),
    TO_CHAR(SYSDATE(), 'YYYY/MM/DD HH24:MI:SS AM'),
    TO_CHAR(SYSDATE(), 'DS HH:MI:SS PM'),
    TO_CHAR(SYSDATE(), 'Month'),
    TO_CHAR(NULL, 'HH:MI:SS');

The returned result is as follows:

+---------------------+------------------------------------------------+--------------------------------------+-----------------------------+---------------------------+
| TO_CHAR(SYSDATE())  | TO_CHAR(SYSDATE(), 'YYYY/MM/DD HH24:MI:SS AM') | TO_CHAR(SYSDATE(), 'DS HH:MI:SS PM') | TO_CHAR(SYSDATE(), 'Month') | TO_CHAR(NULL, 'HH:MI:SS') |
+---------------------+------------------------------------------------+--------------------------------------+-----------------------------+---------------------------+
| 2025-03-04 09:38:56 | 2025/03/04 09:38:56 AM                         | 3/4/2025 09:38:56 AM                 | March                       |                      NULL |
+---------------------+------------------------------------------------+--------------------------------------+-----------------------------+---------------------------+
1 row in set (0.001 sec)

References

• TO_CHAR(character)
• TO_CHAR(number)