Datetime format models|V4.3.3| docs|Distributed Database

Datetime format models

Last Updated：2025-11-27 02:38:06 Updated

Datetime format models specify the format of datetime data stored in the database.

The total length of a datetime format model cannot exceed 22 characters. If you need to convert a string value from a nondefault format to the datetime format, you can use datetime format models in functions.

A datetime format model consists of one or multiple datetime format elements. To convert a string value into a date value, you must follow the conversion rules. For more information, see String-to-date conversion rules.

The RR datetime format element is similar to the YY datetime format element, but the RR datetime format element provides additional flexibility for storing date values across centuries. For more information about the RR datetime format element, see RR datetime format element.

Datetime format models in functions

Datetime format models can be used in the following functions:

To convert a character value in a non-default format into a datetime value, you need to use parameters of the TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ functions to specify the format of the datetime value.
To convert a datetime value into a character value in a non-default format, you need to use parameters of the TO_CHAR function to specify the format of the output string.

If no format is specified, the default format is used for conversion. You can explicitly specify the default datetime format by using the NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, or NLS_TIMESTAMP_TZ_FORMAT system variable. You can query the V$NLS_PARAMETERS system view for values of these variables, or execute the ALTER SESSION or ALTER SYSTEM statement to modify the values of these variables.

Datetime format elements

A datetime format model consists of one or more datetime format elements. For more information about the format elements supported by OceanBase Database, see Datetime format elements section.

If a datetime format is used to parse an input string, the same format element cannot appear twice. Format elements that represent similar information cannot be combined. For example, you cannot use both SYYYY and BC elements in the same datetime format. This rule is intended to prevent conflicts caused by information redundancy.
Some elements cannot be used in the TO_CHAR, TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ functions.
Datetime format elements FF, TZD, TZH, TZM, and TZR can be used in timestamp and interval format models, but not in the DATE format model.
Some elements generate fixed-length strings. If the length of a string is less than the specified length, spaces are added to the end of the string.

Notice

We recommend that you use the 4-digit year element (YYYY). Short year elements may affect query optimization because the year can only be determined at runtime.

Datetime format elements

Element	Applicable to TO_* functions	Description
- / , . ; : "Text"	Yes	A symbol string constant.
AD A.D.	Yes	The AD indicator, with or without periods.
AM A.M.	Yes	The hours before noon, with or without periods.
BC B.C.	Yes	The BC indicator, with or without periods.
CC SCC	No	The century.
D	Yes	The day of a week. Value range: [1,7].
DAY	Yes	The name of the day. The length of the result is fixed and is the same as the maximum length of the name.
DD	Yes	The day of a month. Value range: [1,31].
DDD	Yes	The day of a year. Value range: [1,366].
DY	Yes	The abbreviated name of the day.
FF [1..9\ ]	Yes	The fractional seconds. Use digits 1 to 9 to specify the number of digits in the fractional second portion of the value returned, which is the precision. Use the default precision specified for the datetime data type. It is valid in the timestamp and interval formats, but not in the `DATE` format.
HH	Yes	The hour of the day. Value range: [1,12].
HH12	No	The hour of the day. Value range: [1,12]. It is used in the 12-hour format.
HH24	Yes	The hour of the day. Value range: [0,23]. It is used in the 24-hour format.
IW	No	The week of a year, based on the ISO standard. Value range: [1,52] or [1,53].
IYY IY I	No	The last three digits, two digits, or one digit of a year based on the ISO standard.
IYYY	No	A 4-digit year based on the ISO standard.
J	Yes	The Julian Day.
MI	Yes	The minute of an hour. Value range: [0,59].
MM	Yes	The month of a year. Value range: [01,12]. `01` indicates January, and so forth.
MON	Yes	The abbreviated name of the month.
MONTH	Yes	The full name of the month. The length of the result is fixed and is the same as the maximum length of the name.
PM P.M.	No	The hours after noon, with or without periods.
Q	No	The quarter. Value range: [1,4]. Quarter 1 spans from January to March, and so forth.
RR	Yes	The last two digits of the year. The century depends on the two digits you enter and whether the current year is in the first or second half of the century. If the current year is in the first half of the century, the result is in the current century when you enter a value ranging from 00 to 49, and the result is in the last century when you enter a value ranging from 50 to 99. For example, if the current year is 2060, the result is in the next century when you enter a value ranging from 00 to 49, and the result is in the current century if you enter a value ranging from 55 to 99.
RRRR	Yes	The year. You can enter two or four digits. If you enter two digits, the rule is the same as that of the `RR` element.
SS	Yes	The seconds. Value range: [0,59].
SSSSS	Yes	The seconds of the current day. Value range: [0,86400].
TZD	Yes	The abbreviated time zone string that contains the daylight saving time information. For example, PST indicates US/Pacific Standard Time, and PDT indicates US/Pacific Daylight Saving Time. It is valid in the timestamp and interval formats, but not in the `DATE` format.
TZH	Yes	The hour part of the time zone. It is valid in the timestamp and interval formats, but not in the `DATE` format.
TZM	Yes	The minute part of the time zone. It is valid in the timestamp and interval formats, but not in the `DATE` format.
TZR	Yes	The region information of the time zone, such as US/Pacific. It is valid in the timestamp and interval formats, but not in the `DATE` format.
WW	No	The week of a year. Value range: [1,53].
W	No	The week of a month. Value range: [1,5].
X	Yes	The local delimiter used to delimit the fractional part. For example, you can use it like this: "HH:MI:SSXFF".
Y,YYY	Yes	A year with a comma (,).
YEAR SYEAR	No	A year with a detailed description.
YYYY	Yes	A 4-digit year. The current version does not support BC dates.
YYY YY Y	Yes	The last three digits, two digits, or one digit of a year.

Note

The datetime functions are TO_CHAR, TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ. Notice

The conversions in the table require matching between the dates in the input string and the format elements. Otherwise, an error is returned.
The default date format for OceanBase Database is DD-MON-RR. You can execute ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; to change the datetime format of the current session.

If you omit some format elements, the system returns an error:

obclient> SELECT TO_DATE( '31 Aug 2020', 'DD MON YYY' ) FROM DUAL;
OBE-01830: date format picture ends before converting entire input string

Uppercase letters in date format elements

The case of date format elements affects the case of the generated string. For example, MONDAY is generated based on DAY, Monday is generated based on Day, and monday is generated based on day. Here are some examples:

obclient> SELECT TO_CHAR(sysdate,'mon')  AS nowMonth FROM DUAL;
+----------+
| NOWMONTH |
+----------+
| mar      |
+----------+
1 row in set

obclient> SELECT TO_CHAR(sysdate,'MON')  AS nowMonth FROM DUAL;
+----------+
| NOWMONTH |
+----------+
| MAR      |
+----------+
1 row in set

Punctuation and character literals in datetime format models

You can add the following characters to a datetime format model, which appear in the same positions in the response:

Punctuation marks, such as hyphens (-), slashes (/), commas (,), periods (.), and colons (:)
Character literals, which are enclosed in double quotation marks (")

OceanBase Database can flexibly convert strings into dates. When you use the TO_DATE function, if each numeric element in the input string contains the maximum number of digits allowed, the formatted string matches the input string.

Examples

Example 1: In the format element MM/YY, 02 corresponds to MM, and 07 corresponds to YY.

obclient> SELECT TO_CHAR(TO_DATE('0207','MM/YY'),'MM/YY') FROM DUAL;
+------------------------------------------+
| TO_CHAR(TO_DATE('0207','MM/YY'),'MM/YY') |
+------------------------------------------+
| 02/07                                    |
+------------------------------------------+
1 row in set

Example 2: OceanBase Database allows matching between nonalphanumeric characters and punctuation marks in the format model, such as # and /.

obclient> SELECT TO_CHAR (TO_DATE('02#07','MM/YY'), 'MM/YY') FROM DUAL;
+-------------------------------------------+
| TO_CHAR(TO_DATE('02#07','MM/YY'),'MM/YY') |
+-------------------------------------------+
| 02/07                                     |
+-------------------------------------------+
1 row in set

Date format elements and globalization support

In OceanBase Database, the language of datetime format elements is specified by the NLS_DATE_LANGUAGE and NLS_LANGUAGE parameters. The default value is AMERICAN, which cannot be changed, and thus cannot be globalized.

Example: The default language of the datetime format models is American. Other languages are not supported.

SELECT TO_CHAR (SYSDATE, 'DD/MON/YYYY', 'nls_date_language=''Traditional Chinese'' ') FROM DUAL;

An error is returned in the query result, because the value of the language parameter is not supported.

OBE-12702: invalid NLS parameter string used in SQL function