Purpose
The TO_CHAR function converts a value of date or time type to a VARCHAR data type.
Note
For OceanBase Database V4.3.5, this function is supported starting from V4.3.5 BP1.
Syntax
TO_CHAR(datetime [, fmt])
Parameters
datetime: The data of datetime type to be converted.fmtOptional. Specifies the output format. If not specified, it is converted to aVARCHARdata type with the format specified by thedatetimeparameter. For more information, see fmt.
fmt
The fmt parameter specifies the data storage format. Valid values are as follows:
| Format | Description |
|---|---|
| '-', '/', ',', '.', ';', ':' and "text" | The time-separator characters. In addition to the standard separators, you can use text as a separator. For example, TO_CHAR(SYSDATE(), 'YYYY "year" mm "month" dd "day"'). |
AD/A.D. |
Represents the Common Era. The conversion may vary based on the settings of the NLS. |
AM/A.M. |
Indicates the time is in the morning, like PM/P.M.. The time will indicate AM or PM. |
BC/B.C. |
Indicates before Christ. |
CC/SCC |
The century, as an Arabic numeral. If the last two digits of the year are between 01 and 99, the value returned is the first two digits plus 1. Otherwise, the first two digits are returned. |
D |
Returns a value between 1 and 7, representing the day of the week. |
DAY |
Returns the name of the day of the week.
NoteThe format and language setting affect this function. In China, |
DD |
Returns a number from 1 to 31, representing a day of the month. |
DDD |
Returns the day of the year (1 to 366). |
DL |
Return date in a long date format. It is controlled by the NLS_TERRITORY and NLS_LANGUAGE parameters. |
DS |
Returns the date in the short date format. It is controlled by NLS_TERRITORY and NLS_LANGUAGE. |
E |
Represents the era abbreviation, which is suitable only for the following calendars: Japanese imperial era, Republic of China era, and Buddhist Era of Thailand. |
FF [1..9] |
Milliseconds, and if a number is not specified, the default precision is used. It can be used only with the TIMESTAMP type. |
FM |
Does not return any values. |
HH/HH12 |
Indicates the hour, which is in the 12-hour format (1 to 12). |
HH24 |
The hour, in 24-hour format (0 to 23). |
IW |
The ISO standard week number (1 to 52 or 1 to 53). |
IYYY、IYY、IY、I |
Return the ISO year in 4, 3, 2, and 1 digits (right-padded). |
J |
Represents the Julian day (used in astronomy), calculated as the number of days since January 1st, 4712 B.C. The formula is approximately (Julian year + 4712) * average number of days per year. |
MI |
Returns the minute (0 to 59). |
MM |
The two-digit month number (01 to 12). |
MON |
Returns the abbreviated name of the month, depending on the setting of NLS_DATE_LANGUAGE. For example, if the value of NLS_DATE_LANGUAGE is SIMPLIFIED CHINESE, 4 is returned. |
MONTH |
Returns the name of the month, based on the value of the NLS_DATE_LANGUAGE parameter. For example, 04 means April in Chinese. |
PM/P.M. |
Indicating that it is in the afternoon. It returns the indicator for either a.m. or p.m. |
Q |
The quarter of the year, from 1 to 4. |
RR |
Represents the year (last two digits). |
SS |
Returns seconds (0 through 59) since the minute began. |
SSSSS |
The number of seconds that have elapsed since midnight in a 24-hour day. This value ranges from 0 to 86399. |
SYYYY |
A four-digit year with the S prefix, indicating the year before Christ (BC). |
TZD |
Daylight Saving Time (DST) information in the format of the time zone abbreviation with DST, which must match the time zone format specified by TZR. It includes the following three time zone-related formats: TZH, TZM, and TZR. These are not directly used in TO_CHAR but are related to the time zone. |
TZH |
Hours in the time zone, for example hh:mi:ss.fftzh:tzm. |
TZM |
Minutes in the time zone. |
TZR |
The region information of the time zone, which must be a supported time zone of the database, for example, US/Pacific. |
WW |
The number of the week in the year, starting from 1 to 53. The week starts with the first day of the year and the end is the last day of the seventh week. For example, 2008-01-01 to 2008-01-07 is numbered as 1 and 2008-01-09 to 2008-01-13 as 2. |
W |
The sequence number of the week in a month. |
X |
Represents the local root symbol, which has no particular use and can only be used with the TIMESTAMP type. |
Y,YYY |
Returns a four-digit year, with an English comma separator. For example, 2,008. |
YYYY , YYY , YY , or Y |
Specifies the year as a four-digit, three-digit, two-digit, or one-digit number, respectively. For example, 2008 can be written as 2008, 008, 08, or 8. |
Return type
- Returns
VARCHARdata. - If the
datetimeparameter isNULL, the procedure returnsNULL.
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 result is returned 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