Datetime format models |V3.2.4|OceanBase Database| docs|Distributed Database

Datetime format models

Last Updated：2023-10-24 09:23:04 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 non-default format to the datetime format, you can use datetime format models in functions.

You can use datetime format models in the following functions:

To convert a character value in a non-default format into a datetime value, you need to use the 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 the 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 values of the variables in the V$NLS_PARAMETERS system view or run the ALTER SESSION or ALTER SYSTEM command to modify their values.

Datetime format elements

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

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 a 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, or TO_TIMESTAMP_TZ function.
Datetime format elements including FF, TZD, TZH, TZM, and TZR can be used in timestamp and interval formats, but not in the DATE format.
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 the TO_* function or not	Description
- / , . ; : "Text"	Yes	A symbol string constant.
AD A.D.	Yes	The years of the Common Era, with or without periods (.).
AM A.M.	Yes	The hours before noon, with or without periods (.).
BC B.C.	Yes	The years before the Common Era, with or without periods (.).
CC SCC	No	The century.
D	Yes	The day of the week. Valid values: 1 to 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 the month. Valid values: 1 to 31.
DDD	Yes	The day of the year. Valid values: 1 to 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. This element is valid in timestamp and interval formats, but not in the `DATE` format.
HH	Yes	The hour of the day. Valid values: 1 to 12.
HH12	No	The hour of the day, in 12-hour clock. Valid values: 1 to 12.
HH24	Yes	The hour of the day, in 24-hour clock. Valid values: 0 to 23.
IW	No	The week of the year, based on the ISO standard. Valid values: 1 to 52 or 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 the hour. Valid values: 0 to 59.
MM	Yes	The month of the year. Valid values: 01 to 12. 01 indicates January, and so on.
MON	Yes	The abbreviated name of a month.
MONTH	Yes	The full name of a 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 of the year. Valid values: 1, 2, 3, and 4. Quarter 1 spans January to March, and so on.
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 00 to 49, and the result is in the last century when you enter 50 to 99. For example, if the current year is 2060, the result is in the next century when you enter 00 to 49, and the result is in the current century if you enter 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. Valid values: 0 to 59.
SSSSS	Yes	The seconds of the current day. Valid values: 0 to 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. This element is valid in timestamp and interval formats, but not in the `DATE` format.
TZH	Yes	The hour part of the time zone. This element is valid in timestamp and interval formats, but not in the `DATE` format.
TZM	Yes	The minute part of the time zone. This element is valid in timestamp and interval formats, but not in the `DATE` format.
TZR	Yes	The region information of the time zone. Example: US/Pacific. This element is valid in timestamp and interval formats, but not in the `DATE` format.
WW	No	The week of the year. Valid values: 1 to 53.
W	No	The week of the month. Valid values: 1 to 5.
X	Yes	The local delimiter used to delimit the fractional part. Example: "HH:MI:SSXFF".
Y,YYY	Yes	A year with a comma (,).
YEAR SYEAR	No	A year with a detailed description.
YYYY SYYYY	Yes	A 4-digit year. S prefixes BC dates with a minus sign (-).
YYY YY Yes	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. To display this format, 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 elements in the format string, the system returns an error:

obclient> SELECT TO_DATE( '31 Aug 2020', 'DD MON YYY' ) FROM DUAL;
ORA-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. Example:

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

Punctuations and string constants in datetime format models

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

Punctuations, such as hyphens (-), slashes (/), commas (,), periods (.), and colons (:)
String constants, 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 allowed number of digits, 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 non-alphanumeric characters and punctuations in the format model, such as # for /.

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