All Products
Search
Document Center

Date literals

Last Updated: Jun 18, 2021

You can use a string to specify a date literal. You can also use a TO_DATE function to convert a character or numeric value to a date value. You can use a TO_DATE expression in place of a string to specify a date literal. This applies to only date literals.

TO_DATE('2020-03-25 11:05:00', 'YYYY-MM-DD HH24:MI:SS')

When you use a date value to specify a date literal, you must use the date value of the Gregorian calendar. You can also use the ANSI format to specify a date literal, as shown in the following example. The ANSI date literal does not contain the time information and must be specified in the YYYY-MM-DD format.

DATE '2020-03-25'

You can also use the default date value of the database to specify a date literal. If the default value is used in a date expression, ApsaraDB for OceanBase automatically converts the character value in the default date format to a date value. The default date value of the database is specified by the initialization parameter NLS_DATE_FORMAT. In this example, the default format is DD-MON-RR.

TO_DATE('25-FEB-20', 'DD-MON-RR')

If you specify a date value that does not include the time part, the default time is midnight (00:00:00 in the 24-hour clock and 12:00:00 in the 12-hour clock). If a specified date value does not include the date part, the default date is the first day of the current month.

The DATE column of a table in ApsaraDB for OceanBase always contains both the date and time fields. Therefore, if you query the DATE column, you must specify the time field in the query or make sure that the time field in the DATE column is specified as midnight. Otherwise, the database may not return the query result that you expect. For example, create a Date_Literals table that contains the id column and the date column datecol.

CREATE TABLE Date_Literals (id NUMBER, datecol DATE);

Insert the system date and time SYSDATE of the current session into the table. In this example, the TRUNC function is used to set the time field to midnight. The TRUNC function truncates the date part of SYSDATE. This way, 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 following data in the table is available:

+------+---------------------+
| id   | datecol             |
+------+---------------------+
|    1 | 25-FEB-20 11:28:16  |
|    2 | 25-FEB-20 00:00:00  |
+------+---------------------+

If the query does not contain the time information, you can use the greater than or less than condition instead of the equal to or not equal to condition in the query:

SELECT * FROM Date_Literals WHERE datecol > TO_DATE('2020-02-24', 'YYYY-MM-DD');

The following result is returned:

+------+---------------------+
| id   | datecol             |
+------+---------------------+
|    1 | 25-FEB-20 11:28:16  |
|    2 | 25-FEB-20 00:00:00  |
+------+---------------------+

If you use the equal to condition, only the date whose time information is midnight is returned because the query does not contain the time information.

SELECT * FROM Date_Literals WHERE datecol = TO_DATE('2020-02-25', 'YYYY-MM-DD');

The following result is returned:

+------+---------------------+
| id   | datecol             |
+------+---------------------+
|    2 | 25-FEB-20 00:00:00  |
+------+---------------------+

You can also filter out the time field in the datecol column and query only the date field:

SELECT * FROM Date_Literals WHERE TRUNC(datecol) = DATE '2020-02-25';

The following result is returned:

+------+---------------------+
| id   | detacol             |
+------+---------------------+
|    1 | 25-FEB-20 11:28:16  |
|    2 | 25-FEB-20 00:00:00  | 
+------+---------------------+