Datetime formatting

Datetime formatting specifies the format of date and time data that is stored in databases. The total length of a string that is obtained after datetime formatting cannot exceed 22 characters.

Datetime formatting in functions

Datetime formatting appears in the following data type conversions:

• When you use the TO_DATE, TO_TIMESTAMP, or TO_TIMESTAMP_TZ function to convert a character value in a non-default format to a datetime value, specify the datetime format. To specify the datetime format, set the corresponding parameter of the function that you use.

• When you use the TO_CHAR function to convert a datetime value to a character value in a non-default format, set the corresponding parameter of the function to specify the format.

You can use the following methods to specify the datetime format:

• Explicitly specify the datetime format by using the NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, or NLS_TIMESTAMP_TZ_FORMAT session parameter.

• Implicitly specify the datetime format by using the NLS_TERRITORY session parameter

• Change the default datetime format of a session by executing the ALTER SESSION statement.

Datetime formatting

A datetime formatting model consists of one or more datetime format elements. For more information about the format elements that are supported by ApsaraDB for OceanBase, see the table of datetime format elements.

• In a formatting string, the same format element cannot appear twice, and format elements that represent similar information cannot be combined. For example, you cannot use SYYYY and BC elements in the same formatting string.

• All of the format elements can be used in TO_CHAR, TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ functions.

• The following datetime format elements can be used in timestamp and interval formatting, but not in DATE formatting: FF, TZD, TZH, TZM, and TZR.

• Many datetime format elements are padded with blank spaces or zero values to a specified length.

Notice

In ApsaraDB for OceanBase, we recommend that you use the four-digit year element (YYYY). The year element with a shorter length affects query optimization because the year can be determined only at the runtime.

Table of datetime format elements

Note

Datetime functions are TO_CHAR, TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ.

Take note of the following point: In the preceding conversions, the input date string must match the format elements. Otherwise, an error is returned. The following example is provided:

SELECT TO_DATE( '31 Aug 2020', 'DD MON YYYY' ) FROM DUAL;

+----------------------------------+
| TO_DATE('31AUG2020','DDMONYYYY') |
+----------------------------------+
| 2020-08-31 00:00:00              |
+----------------------------------+

If some elements are missing from your formatting string, the system returns an error:

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

Uppercase letters in spelled-out words, abbreviations, or Roman numerals are also capitalized in the corresponding format elements. For example, the DAY date format element generates MONDAY where every letter is capitalized. Day generates Monday that is in the same format as the Day element. day generates monday that is in the same format as the day element.

OceanBase(SYS@SYS)>SELECT TO_CHAR(sysdate,'mon')  AS nowMonth FROM DUAL;
+----------+
| NOWMONTH |
+----------+
| sep      |
+----------+

OceanBase(SYS@SYS)>SELECT TO_CHAR(sysdate,'MON')  AS nowMonth FROM DUAL;
+----------+
| NOWMONTH |
+----------+
| SEP      |
+----------+

Punctuations and character literals in datetime formatting

Date formatting is required for the following characters. These characters appear in the return value in the same positions as they appear in the formatting string:

• Punctuations, such as hyphens (-), slashes (/), commas (,), periods (.), and colons (:)

• Character literals, which are enclosed in double quotation marks (")

ApsaraDB for OceanBase can convert strings to dates based on your business needs. If you use the TO_DATE function and each numeric element of the input string contains the maximum number of digits allowed for formatting, the formatting string matches the input string.

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

Execute the following statement:

SELECT TO_CHAR(TO_DATE('0207','MM/YY'),'MM/YY') FROM DUAL;

The following query result is returned:

+------------------------------------------+
| TO_CHAR(TO_DATE('0207','MM/YY'),'MM/YY') |
+------------------------------------------+
| 02/07                                    |
+------------------------------------------+

• Example 2 : ApsaraDB for OceanBase allows matching between punctuation characters in formatting and non-alphanumeric characters. For example, # corresponds to / .

Execute the following statement:

 SELECT TO_CHAR (TO_DATE('02#07','MM/YY'), 'MM/YY') FROM DUAL;

The following query result is returned:

+-------------------------------------------+
| TO_CHAR(TO_DATE('02#07','MM/YY'),'MM/YY') |
+-------------------------------------------+
| 02/07                                     |
+-------------------------------------------+

Date format elements and globalization support

In ApsaraDB for OceanBase, you can use the NLS_DATE_LANGUAGE and NLS_LANGUAGE parameters to specify the language for datetime format elements. The default value is AMERICAN. The value cannot be changed. Therefore, globalization is not supported.

Example : By default, the language for datetime format elements is American. The 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 specified value for the language parameter is not supported.

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

More information

• String-to-date conversion rules