DATE data type

The DATE data type stores the date and time information. Although you can use both character and numeric data types to represent the date and time information, the DATE data type has special associated properties.timetime For each DATE value, ApsaraDB for OceanBase stores the information about year, month, day, hour, minute, and second, but does not contain the time zone information.time zoneinfo

Format

NLS_DATE_FORMAT determines the default input and output formats of the DATE data type. Run the following SQL statement to query the default format:

SELECT @@NLS_DATE_FORMAT FROM DUAL;

Return result:

DD-MON-RR

To customize data formats, you can use conversion functions. When you insert data, you can use the TO_DATE(char,fmt) function to specify the input format of the data. When you query data, you can use the TO_CHAR(datetime,fmt) function to specify the output format of the data. These two conversion functions convert a string to the format defined by the fmt parameter. If you do not specify fmt, the default format is used. Notice

The DATE data type stores the time information such as the hour, minute, and second. However, the default format does not contain the time information.

Value range

0001-01-01 00:00:00~9999-12-31 23:59:59

Examples

• Example 1 : This example returns the current system date. Assume that you do not specify the fmt parameter, the TO_CHAR function returns a value in the default format of the data type.

SELECT TO_CHAR(sysdate) FROM DUAL;

Return result:

+------------------+
| TO_CHAR(SYSDATE) |
+------------------+
| 24-FEB-20        |
+------------------+

• Example 2 : Assume that you do not specify the DATE value as a literal, the database returns the system default value:

This example uses the TO_DATE(string,format) function to insert date data. TO_DATE converts the characters in the string parameter to a value in the format specified in the format parameter.

Assume that you issue the following query in February 2020:

SELECT TO_CHAR(TO_DATE('2020', 'YYYY'),'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

Return result:

+----------------------------------------------------------+
| TO_CHAR(TO_DATE('2020', 'YYYY'),'YYYY-MM-DD HH24:MI:SS') |
+----------------------------------------------------------+
|                                       2020-02-01 00:00:00|
+----------------------------------------------------------+