A date (DATE) literal can be specified by using a string or by using the TO_DATE function to convert a character or numeric value to a date value.
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 specify a date literal by using ANSI. ANSI date literals do not contain time information and must be specified in the YYYY-MM-DD format. The syntax is as follows:
DATE '2020-03-25'
In addition, you can specify a date literal by using the default date value of the database. 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 a time component, 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 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. If you want to display the time value, you can execute the ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR HH24:MI:SS'; statement to modify the value of the variable NLS_DATE_FORMAT. Therefore, if 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 datecol column automatically fills in the default midnight time:
INSERT INTO Date_Literals VALUES (1,SYSDATE);
INSERT INTO Date_Literals VALUES (2,TRUNC(SYSDATE));
At this point, query the data in the table 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 a 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 result only returns 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
Conversely, you can 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