You can specify a DATE value as a string literal. You can also use the TO_DATE function to convert a character or numeric value into a date value.
Date literals are the only case where OceanBase Database accepts a TO_DATE expression in place of a string literal. Here is an example:
TO_DATE('2020-03-25 11:05:00', 'YYYY-MM-DD HH24:MI:SS')
When you specify a date value as a literal, you must use the Gregorian calendar. You can also specify the date value as an ANSI literal. The ANSI literal does not contain time information and must be specified in the YYYY-MM-DD format. Here is an example:
DATE '2020-03-25'
OceanBase Database allows you to specify the default date value of the database as a date literal. When you use the default value in a date expression, OceanBase Database automatically converts a character value in the default date format to a date value. The default date format of the database is specified by the initialization parameter NLS_DATE_FORMAT. The default format in the following example is DD-MON-RR:
TO_DATE('25-FEB-20', 'DD-MON-RR')
If you specify a date value without a time component, the default time is midnight (00:00:00 for the 24-hour format and 12:00:00 for the 12-hour format). If you specify a date value without a date component, the default date is the first day of the current month.
The default date format of OceanBase Database is DD-MON-RR. To display both date and time values, you can execute the ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR HH24:MI:SS'; statement to change the value of the NLS_DATE_FORMAT variable. Therefore, to query a DATE column, you must specify the time field in the query, or ensure that the time fields in the DATE are set to midnight. Otherwise, the database may not return the query results you expect.
For example, create a table named Date_Literals that contains the ID and datecol columns:
CREATE TABLE Date_Literals (id NUMBER, datecol DATE);
Insert the system time SYSDATE of the current session into the table. Use the TRUNC() function to set the time field to midnight. The TRUNC() function truncates the date portion of SYSDATE, so that the datecol column is automatically populated with the default midnight value:
INSERT INTO Date_Literals VALUES (1,SYSDATE);
INSERT INTO Date_Literals VALUES (2,TRUNC(SYSDATE));
INSERT INTO Date_Literals VALUES (3, TO_DATE('2020-02-25', 'YYYY-MM-DD'));
The return result is as follows:
obclient> SELECT * FROM Date_Literals;
+------+--------------------+
| ID | DATECOL |
+------+--------------------+
| 1 | 06-NOV-24 11:18:04 |
| 2 | 06-NOV-24 00:00:00 |
| 3 | 25-FEB-20 00:00:00 |
+------+--------------------+
3 rows in set
If you do not specify time information in the query statement, you can use a greater-than or less-than condition in the query statement instead of an equality or inequality condition. Here is an example:
obclient> SELECT * FROM Date_Literals WHERE datecol > TO_DATE('2020-02-24', 'YYYY-MM-DD');
+------+--------------------+
| ID | DATECOL |
+------+--------------------+
| 1 | 06-NOV-24 11:18:04 |
| 2 | 06-NOV-24 00:00:00 |
| 3 | 25-FEB-20 00:00:00 |
+------+--------------------+
3 rows in set
If you use an equality condition, only the date value with midnight is returned as the time because time information is not specified in the query statement. Here is an example:
obclient> SELECT * FROM Date_Literals WHERE datecol = TO_DATE('2020-02-25', 'YYYY-MM-DD');
+------+--------------------+
| ID | DATECOL |
+------+--------------------+
| 3 | 25-FEB-20 00:00:00 |
+------+--------------------+
1 row in set
Likewise, you can filter out the time fields in the datecol column to retrieve only the date fields. Here is an example:
obclient> SELECT ID, TO_CHAR(datecol, 'YYYY-MM-DD') AS datecol_only FROM Date_Literals WHERE TRUNC(datecol) = DATE '2020-02-25';
+------+--------------+
| ID | DATECOL_ONLY |
+------+--------------+
| 3 | 2020-02-25 |
+------+--------------+
1 row in set