You can specify a date (DATE) literal as a string or convert a character or numeric value to a date value by using the TO_DATE function.
Only date literals can be specified by using the TO_DATE expression instead of a string. The syntax is as follows:
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 a Gregorian calendar date value. You can also use the ANSI date literal. ANSI date literals do not contain time information and must be in the YYYY-MM-DD format. The syntax is as follows:
DATE '2020-03-25'
In addition, you can use the default date value of the database to specify a date literal. When you use the default value in a date expression, OceanBase Database automatically converts the character value of the default date format to a date value. The default date format of the database is specified by the initialization parameter NLS_DATE_FORMAT. For example, the default format is DD-MON-RR.
TO_DATE('25-FEB-20', 'DD-MON-RR')
If you specify a date value without time information, the default time is midnight (00:00:00 in 24-hour format and 12:00:00 in 12-hour format). If you specify a date value without date information, the default date is the first day of the current month.
The default date format in OceanBase Database is DD-MON-RR. If you want to display time values, you can execute the ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR HH24:MI:SS'; command to modify the value of the NLS_DATE_FORMAT variable. Therefore, when you query a DATE column, you must specify a time field in the query or ensure that the time field in the DATE column is set to midnight. Otherwise, the database may not return the expected query results.
For example, create a table named Date_Literals with an id column and a datecol date column.
CREATE TABLE Date_Literals (id NUMBER, datecol DATE);
Insert the current session system date and time SYSDATE into the table. This example uses the TRUNC function to set the time field to midnight. The TRUNC function truncates the date part of SYSDATE, so the time field in the datecol column is automatically filled with the default midnight time:
INSERT INTO Date_Literals VALUES (1,SYSDATE);
INSERT INTO Date_Literals VALUES (2,TRUNC(SYSDATE));
The data in the table is as follows:
obclient> SELECT * FROM Date_Literals;
+------+---------------------+
|ID | DATECOL |
+------+---------------------+
| 1 | 25-FEB-20 11:28:16 |
| 2 | 25-FEB-20 00:00:00 |
+------+---------------------+
2 rows in set
When the query does not contain time information, you can use greater than or less than conditions instead of equal to or not equal to conditions. Here is an example:
obclient> SELECT * FROM Date_Literals WHERE datecol > TO_DATE('2020-02-24', 'YYYY-MM-DD');
+------+---------------------+
| ID | DATECOL |
+------+---------------------+
| 1 | 25-FEB-20 11:28:16 |
| 2 | 25-FEB-20 00:00:00 |
+------+---------------------+
2 rows in set
When you use equal to conditions, the query returns only the dates with midnight time. Here is an example:
obclient> SELECT * FROM Date_Literals WHERE datecol = TO_DATE('2020-02-25', 'YYYY-MM-DD');
+------+---------------------+
| ID | DATECOL |
+------+---------------------+
| 2 | 25-FEB-20 00:00:00 |
+------+---------------------+
2 rows in set
You can also filter out the time field in the datecol column and query only the date field. Here is an example:
obclient> SELECT * FROM Date_Literals WHERE TRUNC(datecol) = DATE '2020-02-25';
+------+---------------------+
| id | detacol |
+------+---------------------+
| 1 | 25-FEB-20 11:28:16 |
| 2 | 25-FEB-20 00:00:00 |
+------+---------------------+
2 rows in set