All Products
Search
Document Center

Datetime formatting

Last Updated: Jun 18, 2021

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

Element

Supported by datetime functions

Description

- / , . ; : "text"

Yes

Punctuations and quoted text are copied to the result.

AD A.D.

Yes

The anno domini (A.D.) year. The periods (.) can be retained or removed.

AMA.M.

Yes

The morning. The periods (.) can be retained or removed.

BCB.C.

Yes

The before Christ (B.C.) year. The periods (.) can be retained or removed.

D

Yes

The day (1 to 7) of the week.

DAY

Yes

The name of the day.

DD

Yes

The day (1 to 31) of the month.

DDD

Yes

The day (1 to 366) of the year.

DL

Yes

Date and time data is printed only in the fixed format. For example, "Monday, January, 01, 1996" is in the fixed format.

DS

Yes

Date and time data is printed only in the fixed format. For example, "10-10-1996" is in the fixed format.

DY

Yes

The abbreviated name of the date. The day of the week is returned.

FF [1..9]

Yes

The fractional seconds. Use the numbers 1 to 9 to specify the number of digits in the fractional second portion of the return value. By default, the precision that is specified by the datetime data type is used. This element is valid in timestamp and interval formatting, but invalid in DATE formatting.

FX

Yes

Requires an exact match between character data and the format model.

HHHH12

Yes

The hour (1 to 12). The 12-hour clock is used.

HH24

Yes

The hour (0 to 23) The 24-hour clock is used.

YYYY

Yes

The year in the four-digit format.

MI

Yes

The minute (0 to 59)

MM

Yes

The month (01 to 12). The value 01 represents January.

MON

Yes

The abbreviated name of the month.

MONTH

Yes

The name of the month.

PMP.M.

Yes

The afternoon. The periods (.) can be retained or removed.

Q

Yes

The quarter (1, 2, 3, and 4). The first quarter lasts from January to March.

RR

Yes

The year in the two-digit format.

RRRR

Yes

The year. Four-digit or two-digit inputs can be entered.

SS

Yes

The second (0 to 59).

SSSSS

Yes

The number of seconds (0 to 86400) after midnight.

TZD

Yes

The daylight saving time (DST) information. The TZD value is an abbreviated time zone string that contains the DST information. This element is valid in timestamp and interval formatting, but invalid in DATE formatting.

TZH

Yes

The time zone hour. This element is valid in timestamp and interval formatting, but invalid in DATE formatting.

TZM

Yes

The time zone minute. This element is valid in timestamp and interval formatting, but invalid in DATE formatting.

TZR

Yes

The region information about the time zone. This element is valid in timestamp and interval formatting, but invalid in DATE formatting.

X

Yes

The decimal point, which is always a period (.).

Y,YYY

Yes

The year with a comma (,).

YYYYSYYYY

Yes

The year in the four-digit format. S means that a minus sign (-) is used to represent a B.C. date.

YYYYYY

Yes

The last one, two, or three digits of the year.

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