All Products
Search
Document Center

Formatting overview

Last Updated: Jun 18, 2021

Formatting specifies the format of datetime or numeric data that is stored in databases. When you convert a string to a datetime or numeric value, the formatting determines how ApsaraDB for OceanBase converts and stores the string. In an SQL statement, you can set the parameters of the TO_CHAR, TO_NUMBER, and TO_DATE functions to specify the following items:

  • The format of the values that are returned by ApsaraDB for OceanBase

  • The format of the values that are stored in ApsaraDB for OceanBase

ApsaraDB for OceanBase supports the following types of data formatting:

Number formatting

Number formatting specifies the formats of fixed-point and floating-point numbers that are stored in databases. When you need to convert NUMBER, BINARY_FLOAT, or BINARY_ DOUBLE values in an SQL statement to VARCHAR2 values, you can use number formatting in functions. A number formatting model consists of one or more number format elements. For more information, see Number formatting.

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. When you need to convert character values in a non-default format to values in the datetime format, you can use datetime formatting in functions. A datetime formatting model consists of one or more datetime format elements. For more information, see Datetime formatting. For more information about the rules for converting strings to date values, see String-to-date conversion rules.

The RR datetime format element is similar to the YY datetime format element. However, the RR element offers additional flexibility for storing the values of dates that are not covered by the current century. For more information about RR, see RR datetime format element.

Formatting modifiers

ApsaraDB for OceanBase does not support FX or FM formatting modifiers.