DATE data type

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

Syntax

The default format of the DATE data type is determined by the NLS_DATE_FORMAT parameter. Execute the following SQL statement to view the default format:

SELECT @@NLS_DATE_FORMAT FROM DUAL;

The return result is as follows:

DD-MON-RR

You can use a conversion function to define a data format. When you want to insert data, you can use the TO_DATE (char,fmt) function to specify the data input format. When you want to query data, you can use the TO_CHAR(datetime,fmt) function to specify the data output format. These two conversion functions convert strings to the format specified by the fmt argument. If the fmt argument is not specified, the default format is used.

Considerations

Although the date and time information can be represented by using character and number data types, the DATE data type has special associated properties. OceanBase Database stores the time information of year, month, day, hour, minute, and second in each DATE value without the time zone information. The value range is [0001-01-01 00:00:00,9999-12-31 23:59:59]. If you do not specify the DATE literal, OceanBase Database returns the default value:

• Year: the current year, which is returned by SYSDATE.

• Month: the current month, which is returned by SYSDATE.

• Day: 01. The default value is the first day of the current month.

• The value is 0 for hour, minute, and second.

Examples

• Execute the following statement to obtain the system date. Because the fmt parameter is not specified, TO_CHAR returns 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
  

• Execute the TO_DATE(string, format) statement to insert a date. TO_DATE converts the strings specified in string to the format defined in 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