DATE data type

The DATE data type stores date and time information. This topic describes the syntax, parameters, and usage of the DATE data type.

Syntax

The default input and output format for the DATE data type is determined by the NLS_DATE_FORMAT parameter. Run the following SQL statement to view the default format:

SELECT @@NLS_DATE_FORMAT FROM DUAL;

The result is as follows:

DD-MON-RR

If you want to customize the data format, you can use conversion functions. When inserting data, you can specify the input format using the TO_DATE(char, fmt) function. When querying data, you can specify the output format using the TO_CHAR(datetime, fmt) function. These two conversion functions convert strings to the format defined by the fmt parameter. If the fmt parameter is not specified, the default format is used.

Considerations

Although date and time information can be represented using character and numeric data types, the DATE data type has special attributes. For each DATE value, OceanBase Database stores the following information: year, month, day, hour, minute, and second, but does not store timezone information. The valid range is from 0001-01-01 00:00:00 to 9999-12-31 23:59:59. If the DATE literal is not specified, the database returns the system default value:

• Year: the current year returned by SYSDATE.

• Month: the current month returned by SYSDATE.

• Day: 01, which is the first day of the current month by default.

• Hour, minute, and second are all 0.

Examples

• Return the current system date. Since the fmt parameter is not specified, TO_CHAR returns the data in the default format of the data type.

  obclient> SELECT TO_CHAR(sysdate) FROM DUAL;
  +------------------+
  | TO_CHAR(SYSDATE) |
  +------------------+
  | 27-OCT-21        |
  +------------------+
  1 row in set
  

• Insert date data using TO_DATE(string, format). The TO_DATE function converts the string in string to the format defined by format.

  obclient> SELECT TO_CHAR(TO_DATE('2020', 'YYYY'),'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
  +--------------------------------------------------------+
  | TO_CHAR(TO_DATE('2020','YYYY'),'YYYY-MM-DDHH24:MI:SS') |
  +--------------------------------------------------------+
  | 2020-10-01 00:00:00                                    |
  +--------------------------------------------------------+
  1 row in set