DATE literals

You can specify a DATE literal as a string, or you can use 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 the Gregorian calendar, and you can also use the ANSI standard to specify a 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, as shown in the following example, where 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 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, 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's 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 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 query result 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, as the query does not contain time information. 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