How to compare dates in Oracle

DATE data type

In Oracle, you can insert a date value in the column using datetime data types. This group stores date and time values, including a year, a month, a day, hours, minutes, seconds, and/or time zone information. The datetime data types include DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE.

The DATE data type stores date and time information, including a century, a year, a month, a date, hours, minutes, and seconds. You can specify the format of the date using the NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, and NLS_DATE_LANGUAGE parameters.

For date comparison, you can use the comparison operators, such as Equals, Greater Than, Less Than, etc. However, as mentioned before, you should have the same date format for the column you retrieve and define in the statement. If they mismatch, you can convert the date into a string either as a date literal or using the TO_DATE function.

The date literals are used to specify the date value only in the format 'YYYY-MM-DD' without adding time information. However, in Oracle, a date datatype has a time element, that's why by default the date will be displayed as 'YYYY-MM-DD 00:00:00'. The syntax of the date literal is as follows:

DATE 'YYYY-MM-DD'
                    

In the example, we will retrive payment dates greater than July 15, 2022. However, for date comparison, we will add the date literal to match the format string:

Also, you can use the TO_DATE function to convert a string into a value of the DATE data type using the format mask. However, note that this function cannot be used for transforming the value in the other datetime data types. The syntax of the Oracle TO_DATE function is as follows:

TO_DATE( string_value, [format_mask], [nls_date_language] )
                    

The parameters mean the following:

• string_value: The string value that you want to convert into a date value.
• [format_mask]: The format of the character or string value, in which the value was provided.
• [nls_date_language]: The way of how the output date value will be displayed.

Note that the last two parameters are optional. Also, you should take into account that the output date will be displayed according to the specified NLS_DATE_LANGUAGE and NLS_DATE_FORMAT parameters.

To illustrate how to compare dates using the TO_DATE() function, we retrieve a list of payments that were received after July 15, 2022. To do that, we execute the SELECT statement with the TO_DATE function in the WHERE clause and the Greater Than comparison operator. The result is as follows:

TIMESTAMP data types

The TIMESTAMP data type extends the DATE data type and stores date and time including a year, a month, a day, hours, minutes, seconds, and fractional seconds. The syntax of the TIMESTAMP data type is as follows:

column_name TIMESTAMP[(fractional_seconds_precision)]
                    

where fractional_seconds_precision indicates the number of digits to be displayed in seconds. The range of fractional seconds precision is from 0 to 9 digits. If you do not specify a fractional_seconds_precision parameter, 6 digits are set by default.

To illustrate how the TIMESTAMP works, we execute the SELECT statement. It retrieves the date with the TIMESTAMP data type indicating when information about managers has been updated.

If you want to include time information into the date, you can use a timestamp literal. In the example, we will get information about managers that has been updated after the dates greater than July 15, 2022, precisely after 11:26:00:

If you want to ouput a date without time, you can use a TRUNC() function which returns the DATE value truncated to the specified unit.

The following statement outputs the date that have been truncated to the midnight.

By the way, in dbForge Studio for Oracle, you can customize the predefined date formats indicating how datetime values can be displayed in Data Editor. To do that, on the Tools menu, click Options. In the window that opens, switch to Data Editor > Formatting. There, select the format from the dropdown list, edit it, and click OK to save the changes.

ALTER SESSION

In Oracle, the ALTER SESSION statement can be used to set or modify a parameter for the current session. The statement is valid until you disconnect the database. To view all parameters which can be changed, you can execute the command with v$parameter:

SELECT
    name
FROM
    v$parameter
WHERE
    isses_modifiable= 'TRUE';
                    

where name is the name of the parameter and isses_modifiable notifies whether the parameter can be changed (TRUE) or not (FALSE) at the session level.

From the list, we see that we can set or change the NLS_DATE_FORMAT parameter, which indicates the default date format. The parameter can be used with the TO_CHAR and TO_DATE functions, and can be defined by the NLS_TERRITORY parameter. The syntax is as follows:

NLS_DATE_FORMAT = 'format'
                    

The parameter type is a string, and the range of values takes any valid date format within a fixed length. Now, let's modify the date format for the current connection. To do that, we execute the ALTER SESSION statement with the NLS_DATE_FORMAT parameter, where we set a new format as a string.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY';
                    

To make sure that the changes have been applied, run the following statement:

The following statement displays the updated DATE data type assigned to the payment_date from the Payment table.

Conclusion

In the article, we have considered how to compare Oracle date data types and provided examples performed with the help of dbForge Studio for Oracle, one of the best IDEs to use for database development, administration, testing, analysis, and deployment. dbForge Studio helps write PL/SQL queries quickly and efficiently with the context-sensitive, code completion suggestions, and formatting options supported in visual Text and Data Editors. Moreover, you don't have to use multiple tools for all database-related tasks. All this can be done within a single tool - dbForge Studio.