DATE data type

The DATE data type stores date and time information. This topic describes the syntax, parameters, and usage notes 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 following example shows the returned result of this statement:

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.

Notice

The DATE data type stores time information such as hour, minute, and second. The time information is not contained in the default format.

Usage notes

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. Value range: [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.

Example

• Execute the following statement to restore the system date. Because the fmt argument 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