Datetime format models

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.

Datetime format models can be used in the following functions:

• To convert a character value in a nondefault format to a datetime value, you need to use parameters of the functions TO_DATE, TO_TIMESTAMP and TO_TIMESTAMP_TZ to specify the format of the datetime value.

• To convert a datetime value to a character value in a nondefault 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 values of these variables in the V$NLS_PARAMETERS system view or modify the values of these variables by using the ALTER SESSION or ALTER SYSTEM command.

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 Table of 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 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, blanks 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

If you omit some format elements, 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. Here is an 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

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 allowed number of digits, the formatted string matches the input string.

Example

• 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 # 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