Purpose
This function converts a datetime value to data of the VARCHAR type.
Considerations
For OceanBase Database V4.3.5, this function has been supported starting from V4.3.5 BP1.
Syntax
TO_CHAR(datetime [, fmt])
Parameters
datetime: a datetime value to be converted.fmt: Optional. Specifies the format for the output. If this parameter is not specified, the value is converted to the VARCHAR data type in the format specified by thedatetimeparameter. For more information, see fmt.
fmt
The value of the fmt parameter can be one of the following:
| Syntax | Description |
|---|---|
-, /, ,, ., ;, : and "text" |
Specifies the time separator. You can also use character strings as separators. For example, you can use the following example: TO_CHAR(SYSDATE(), 'YYYY "year" mm "month" dd "day"') |
AD/A.D. |
The common era. It is converted based on the value of NLS to AD or the common era. |
AM/A.M. |
Specifies the period of time from midnight to noon. It is equivalent to PM/P.M. when identifying AM or PM. |
BC/B.C. |
Represents years before the Common Era. |
CC/SCC |
Returns the century as a numeric value. If the last two digits of the year fall between 01 and 99, returns the first two digits plus one. Otherwise, returns the first two digits. |
D |
Returns a number from 1 to 7 indicating the day of the week. |
DAY |
Returns the name of the day of the week, such as Monday or Tuesday.
NoteThe format is affected by the language settings. For example, in a Chinese environment, the value is |
DD |
Returns the day of the month (1 to 31). |
DDD |
Returns the day of the year (1 to 366). |
DL |
Returns the date in the long date format. The format is controlled by the NLS_TERRITORY and NLS_LANGUAGE parameters. |
DS |
Returns a short date format. The format is controlled by the NLS_TERRITORY and NLS_LANGUAGE parameters. |
E |
Indicates the era, but only for the following calendars: Japanese imperial calendar, ROC calendar, and Buddhist Era calendar. |
FF [1..9] |
Indicates milliseconds. If you omit this value, the default precision is used. This option is only available for the TIMESTAMP type. |
FM |
No output is returned. |
HH/HH12 |
Indicates the hour in a 12-hour clock (1-12). |
HH24 |
Represents the hour in 24-hour format (0-23). |
IW |
The ISO standard week number (1 through 52 or 1 through 53). |
IYYY, IYY, IY, and I |
Return the ISO year in 4, 3, 2, or 1 digit, respectively. |
J |
The Julian day number, which is used in astronomy. The Julian day number of a date is calculated by adding 1 January 4712 BCE to the number of Julian days calculated by this method: (era date + 4712) × average length of Julian days per year. |
MI |
Returns the minute (0 to 59). |
MM |
Returns a two-digit month (01 to 12). |
MON |
Returns the abbreviation of the month and is affected by the value of NLS_DATE_LANGUAGE, for example, in the Chinese language environment, 04 returns as 4. |
MONTH |
Name of the month as specified by the setting for NLS_DATE_LANGUAGE. For example, 4 is returned as April when NLS_DATE_LANGUAGE is set to Chinese. |
PM/P.M. |
Indicates afternoon. The result of a query will include an AM or PM designator. |
Q |
Returns a number that indicates the quarter (1 to 4). |
RR |
The last two digits of the year. |
SS |
Seconds (0 to 59) of the minute. |
SSSSS |
Returns the number of seconds since midnight (0 to 86399). |
SYYYY |
A four-digit year, where S is a prefix that indicates a BC year. |
TZD |
Daylight saving time information. It is the time zone abbreviation with Daylight Saving information. The format of TZR must be set. TZD consists of TZH, TZM, and TZR, all of which are related to the time zone and are used in conjunction with the TO_CHAR function. |
TZH |
The hours of the time zone, for example hh:mi:ss.fftzh:tzm. |
TZM |
The minutes in the time zone. |
TZR |
Region information in the time zone, which must be a supported time zone of the database, for example, US/Pacific. |
WW |
Is the number of the week of the year, ranging from 1 to 53. For example, if the date is 2008-01-01 through 2008-01-07, the value is 1. If the date is 2008-01-09 through 2008-01-13, the value is 2. |
W |
Week number of the month. |
X |
Represents the local root symbol, with no specific purpose, and can only be used with the TIMESTAMP data type. |
Y,YYY |
Returns the year in four digits with a comma as a separator. For example, 2,008. |
YYYY , YYY , YY , Y |
The year, from the last four, three, two, or one digits in the year part of the datetime expression. For example, 2008 can be represented as 2008, 008, 08, or 8. |
Return type
- Returns data of the
VARCHARtype. - If the
datetimevalue isNULL, the value is returned asNULL.
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 output 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