Datetime format model

A datetime format model specifies the format in which datetime data is stored in the database.

The total length of a datetime format model cannot exceed 22 characters. When you need to convert a character value in a non-default format to a datetime value, you can use the datetime format model in the function.

A datetime format model consists of one or more datetime formatting elements. If you need to convert a string value to a datetime value, you must follow certain conversion rules. For more information, see Rules for converting strings to dates.

The RR datetime formatting element is similar to the YY datetime formatting element, but it provides additional flexibility for storing cross-century datetime values. For more information about the RR datetime formatting element, see RR datetime formatting element.

Date and time format model in functions

You can use the date and time format model in the following functions:

• When you convert a character value in a non-default format to a date or timestamp value, you must specify the date and time format in the arguments of the TO_DATE, TO_TIMESTAMP, or TO_TIMESTAMP_TZ function.

• When you convert a date or timestamp value to a character value in a non-default format, you must specify the output string format in the arguments of the TO_CHAR function.

If you do not specify a format, the default format is used for conversion. You can explicitly specify the default date and time format by using the system variables NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, or NLS_TIMESTAMP_TZ_FORMAT. You can query the values of these variables in the V$NLS_PARAMETERS system view, and you can modify the values by using the ALTER SESSION or ALTER SYSTEM command.

Elements of a datetime format model

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

• If the format is used to parse an input string, the same format element cannot appear twice, and similar format elements cannot be combined. For example, you cannot use the SYYYY and BC elements in the same datetime format. This rule is to avoid conflicts caused by redundant information.

• Some elements cannot be used in the TO_CHAR, TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ functions.

• The datetime format model elements FF, TZD, TZH, TZM, and TZR can be used in the format of a timestamp or interval, but not in the format of a DATE.

• Some elements generate strings of fixed length. If the length is insufficient, the string is padded with spaces at the end.

  Notice

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

Table of date and time formatting elements

If your format string is missing some elements, you will receive an error message from the system:

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

Capitalization of elements in date format models

The capitalization of elements in the format model affects the capitalization of the generated string. For example, the date format element DAY generates MONDAY, Day generates Monday, and day generates monday. 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 marks and string constants in datetime format models

You can include the following characters in the format model. These characters appear in the return value at the same positions as in the format model:

• Punctuation marks, such as hyphens, slashes, commas, periods, and colons.

• String constants, enclosed in double quotation marks.

OceanBase Database can flexibly convert strings to dates. When you use the TO_DATE function, if each numeric element in the input string contains the maximum number of digits allowed by the format model, the format string will match the input string.

Examples

• Example 1: The format element MM/YY, where 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 non-alphanumeric characters to match the punctuation marks in the format model. # corresponds to /.

  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 formatting elements and globalization support

In OceanBase Database, the language for date and time formatting elements can be specified using the parameters NLS_DATE_LANGUAGE and NLS_LANGUAGE. The default value is AMERICAN, and it cannot be modified, so globalization support is not available.

Example: The default language for date and time formatting parameters is American, and other languages are not supported.

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

The query result returns an error because the language parameter is not supported.

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