Purpose
This function converts a datetime value to data of the VARCHAR type.
Considerations
For OceanBase Database V4.3.5, this function is supported starting from V4.3.5 BP1.
Syntax
TO_CHAR(datetime [, fmt])
Parameters
datetime: specifies a datetime value to convert.fmtOptional. The output format. If this parameter is not specified, the value of this parameter is converted to a VARCHAR type according to the format specified by thedatetimeparameter. For more information, see fmt.
fmt
The fmt parameter takes the following values:
| Format | Description |
|---|---|
-、/、,、.、;、:、" and "text" |
Time separator. In addition to the standard separators, you can use a text string as a separator. For example: TO_CHAR(SYSDATE(), 'YYYY "year" mm "month" dd "day"'). |
AD/A.D. |
Indicates the Anno Domini. It will be converted to either the common era or AD, depending on NLS. |
AM/A.M. |
Designates a.m. It returns a.m. or p.m. as specified by the PM/P.M. designator. |
BC/B.C. |
Specifies the era before Common Era. |
CC/SCC |
The two-digit century in Arabic numerals. If the year is in the 100s, the result is 01; if the year is in the 200s, the result is 02; if the year is in the 300s, the result is 03, and so on. |
D |
The value is a number from 1 to 7 indicating the day of the week. |
DAY |
Returns the name of a particular day of the week.
NoteThe format and language settings affect the output. In a Chinese environment, |
DD |
Returns the day of the month (1 to 31). |
DDD |
Returns the day of the year (1 to 366). |
DL |
Returns the long date format. It is controlled by the NLS_TERRITORY and NLS_LANGUAGE parameters. |
DS |
Returns date in a short format. The value is controlled by NLS_TERRITORY and NLS_LANGUAGE. |
E |
Abbreviation of the era. The E parameter is available for the following calendars: Japanese Imperial, Republic of China, and Buddhist Era. |
FF [1..9] |
The fractional seconds precision. If not specified, the default precision is used. It can be used only with the TIMESTAMP type. |
FM |
Returns no output. |
HH/HH12 |
Specifies the hour in 12-hour format (1 through 12). |
HH24 |
The hour using a 24-hour clock (0 to 23). |
IW |
The week number in the ISO standard (from 1 to 52 or 1 to 53). |
IYYY, IYY, IY, and I |
Returns the year in ISO year format, which consists of 4, 3, 2, or 1 digit, respectively. |
J |
Represents the Julian date, which is used in astronomy. It's calculated from the start of the Julian calendar, which is the 1st of January, 4712 BC. The Julian date is derived using the formula: (Year + 4712) × the average Julian day length of the year. |
MI |
Returns the minutes (0 to 59). |
MM |
Returns the month as a two-digit value (01 to 12). |
MON |
Return the abbreviated name of the month. It is affected by NLS_DATE_LANGUAGE. For example, 04 is 4 in the Chinese environment. |
MONTH |
Returns the name of the month. This setting depends on the NLS_DATE_LANGUAGE parameter, for example, in Chinese, 04 will be displayed as 4. |
AM/A.M. |
Specifies the a.m. time period. Like PM/P.M., the AM/A.M. keyword specifies whether the time is in the a.m. or p.m. period. |
Q |
Returns the quarter (1 to 4). |
RR |
Represents the year, where the value is the last two digits. |
SS |
Returns the second of the minute (0 through 59). |
SSSSS |
Returns the number of seconds (0 to 86399) since midnight. |
SYYYY |
A four-digit year, S indicates the BC period. |
TZD |
Time zone daylight saving information. It contains the abbreviation of the time zone and daylight saving information. The value must be the same as the time zone specified by the TZR format. The value also contains TZH, TZM, and TZR, which relate to the time zone but are not directly used in TO_CHAR. |
TZH |
Hours of the time zone, for example, hh:mi:ss.fftzh:tzm. |
TZM |
Minutes in the timezone. |
TZR |
The region information in the timezone, which must be a supported timezone by the database, such as US/Pacific. |
WW |
Indicates the week of the year, from the first day of the year to the last day of the 7th week in the year (1 through 53). For example, 2008-01-01 to 2008-01-07 is 1, and 2008-01-09 to 2008-01-13 is 2. |
W |
The number of the week of the month. |
X |
Specifies a local root symbol, which has no special use and can be used only with the TIMESTAMP type. |
Y,YYY |
Returns a 4-digit year, with a comma separating the thousands. For example, 2,008. |
YYYY , YYY , YY , Y |
Represents the year using the following number of digits: 4, 3, 2, and 1. For example, 2008 can be represented as 2008, 008, 08, or 8. |
Return type
- Returns
VARCHARtype data. - A
NULLvalue is returned when thedatetimeparameter isNULL.
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 procedure returns the following result:
+---------------------+------------------------------------------------+--------------------------------------+-----------------------------+---------------------------+
| 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