Purpose
This function is used to convert a value of the datetime type to data of the VARCHAR type.
Syntax statement
TO_CHAR(datetime [, fmt])
Parameters
datetime: the datetime data to be converted.fmt: Optional. Specifies the output format. If not specified, the value will be converted to theVARCHARdata type according to the format ofdatetime. For details, see fmt.
fmt
The fmt parameter can be set to the following values:
Format |
Note |
|---|---|
-, /, ,, ., ;, : and "text" |
Represents a time separator. In addition to the standard ones, text can also be used as a separator. For example, TO_CHAR(SYSDATE(), 'YYYY "year" mm "month" dd "day"'). |
AD/A.D. |
Represents the Anno Domini era. Depending on the NLS settings, it may be converted to Anno Domini or AD etc. |
AM/A.M. |
Indicates the morning. Same as PM/P.M. Returns the morning or afternoon identifier. |
BC/B.C. |
B.C. |
CC/SCC |
Return the century as a two-digit Arabic numeral. If the last two digits of the year are between 01 and 99, return the first two digits plus 1; otherwise, return the first two digits. |
D |
Returns a day of the week, with the return value being a serial number from 1 to 7. |
DAY |
Returns a day of the week.
NoteThis format is related to the language settings. In the Chinese environment |
DD |
Return a specific day of the month (1 to 31). |
DDD |
Return a specific day of the year (1 to 366). |
DL |
Return a long date format. Subject to NLS_TERRITORY, NLS_LANGUAGE Parameter control. |
DS |
Return a short date format. Subject to NLS_TERRITORY, NLS_LANGUAGE Parameter control. |
E |
Represents the abbreviation for an era, but is only suitable for the following calendars: Japanese Imperial calendar, Republic of China calendar, and Thai Buddhist calendar. |
FF [1..9] |
It is in milliseconds. If you do not specify a value, the default precision will be used. It can only be used for TIMESTAMP type. |
FM |
Returns nothing. |
HH/HH12 |
Represents hours, in a 12-hour format (1 to 12). |
HH24 |
Represents hours, in a 24-hour format (0 to 23). |
IW |
The ISO week number (ranging from 1 to 52 or 1 to 53). |
IYYY, IYY, IY, I |
Return the year in ISO format (4 digits), with the digits decreasing in order: 4, 3, 2, 1. |
J |
It represents a Julian day (a calendar system commonly used in astronomy). Starting from January 1, 4712 BC, the result is an integer. The general formula is: (Gregorian date + 4712) * average number of days in a Julian year. |
MI |
Return minutes (0 to 59). |
MM |
Return the two-digit month (01 to 12). |
MON |
Abbreviation of the return month. It is related to the country NLS_DATE_LANGUAGE. For example, 04 is represented as 4 in a Chinese-speaking environment. |
MONTH |
Return the name of the month. It is related to the country NLS_DATE_LANGUAGE. For example, in Chinese, 04 represents April. |
PM/P.M. |
Represents afternoon. Same as AM/A.M. Returns the morning or afternoon identifier. |
Q |
Quarter (1 to 4). |
RR |
The last two digits of the year. |
SS |
Number of seconds returned within one minute (0 to 59). |
SSSSS |
Returns the cumulative number of seconds from midnight in a day (0 to 86,399). |
SYYYY |
Four-digit year. The S prefix indicates years before the Common Era (BC). |
TZD |
Daylight saving time information, which consists of the time zone abbreviation followed by the daylight saving time information. It must correspond to the time zone set by the TZR format, including the following three: TZH, TZM, TZR, which are all related to time zones and are not used directly in TO_CHAR. |
TZH |
The hour in the time zone, for example, hh:mi:ss.fftzh:tzm. |
TZM |
Minutes in the time zone. |
TZR |
The time zone region information must be a time zone supported by the database, such as US/Pacific. |
WW |
The serial number of a week, starting from the first day of the year to the seventh day of the last month of the year (1 to 53). For example, 2008-01-01 to 2008-01-07 is considered 1, and 2008-01-09 to 2008-01-13 is considered 2. |
W |
The weekday number of the month. |
X |
Represents the local root symbol. It has no special use and can only be used together with the TIMESTAMP data type. |
Y,YYY |
Return a four-digit year, separated by commas. For example, 2,008. |
YYYY, YYY, YY, Y |
Represents the year, which consists of the last four digits. For example, for the year 2008, the possible values are: 2008, 008, 08, and 8. |
Return type
- Returns data of the
VARCHARtype. - If the
datetimevalue isNULL, returnNULL.
Examples
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 return 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