How to Oracle SQL Compare Dates (6 Ways)

Oracle SQL comparing dates is not complicated but you have to follow the correct syntax and match targeting columns. Otherwise, need to convert column types into the same matching data type. Otherwise you will face comparison fails.

In order to avoid this type of failure, you can follow one of the below options to Oracle SQL Compare Dates by datatype in your table.

Other than comparing options, the article describes the most common wrong ways of comparing dates, the performance impact on queries, and modified date format parameters that are used in the session.

Content available on

Comparative operators
Date literals
TO_DATE () function
TRUNC () Function
Modify Session Parameters
Use Intervals
Wrong ways of comparing dates

A date can be saved as a date data type or as a string. Date data type consists of all the information including YEAR, MONTH, DAY, HOUR, MINUTES, SECONDS, and TIMEZONE. Here I hope to describe the title with SQL examples. Let’s move on to the topic.

I hope to use the MAIN_ORDERS table as my sample data set. Here I wish to as 3 date-related columns but 2 columns in the DATE data type and the other one is VARCHAR. I want to compare dates in SQL in order to filter the dataset according to my filtration. Below is my MAIN_ORDERS table and Sample dataset.

1. Comparing operators

If you know the logical operators in programming those comparison operators are used to compare the two dates. If your column uses date data type then you are free to use these operators for compression. Otherwise, you need to change the format using the lateral or TODATE () function.

You can use = (equal), < (less than),> (Greater than), != (Not Equal), or any other logical operator for a matching date or a mix of those. Let’s check the correct way for Oracle SQL compare dates.

--
SELECT * FROM yourtable 
WHERE yourdate >= '08-OCT-2024';
--

Now let’s try to write the below scenarios using logical operators

Validate START_DATE is later than END_DATE

-- QUERY 1
select * from main_orders
where start_date > end_date
--

According to our dataset, there is no such data that END_DATE earlier than START_DATE. We use logical operators to compare dates. Because both columns are in the same data type.

MAIN_ORDERS that finish(END_DATE) before DEC/01/2023 12:00:00 AM

-- QUERY 2
select * from main_orders
where end_date <= '01-DEC-2023';
--

2. Date Literals

Date literal is another option for matching datatypes in Oracle. But the date literal only supports the ‘YYYY-MM-DD’ format. In the previous query, the date passes in the ‘DD-MMM-YYYY’ format also without any literals but literals can use it only if the date is available in the ‘YYYY-MM-DD’ format. See below a simple example

-- SYNTAX 
DATE 'YYYY-MM-DD'
--

MAIN_ORDERS that finish(END_DATE) before DEC/01/2023 12:00:00 AM

-- QUERY 3
select * from main_orders
where end_date <= date '2023-12-01'; -- support only date 'YYYY-MM-DD' format
--

also use TIMESTAMP format as a literal in your date but it needs to have in the below format. Below YYYY-MM-DD HH:MI:SS are mandatory but others are optional.

-- SYNTAX 
TIMESTAMP 'YYYY-MM-DD HH:MI:SS[.FFFFFFFFF] [{+|-}HH:MI]'
--

3. TO_DATE () Function

TO_DATE () function is the most usable way for Oracle SQL compare dates. This supports any format that can be converted into DATE. Sometimes dates save in the table as VARCHAR; in that time, we have to convert VARCHAR into DATE type, otherwise, it raises an error when running.

-- SYNTAX : 
TO_DATE( string_value, [format_mask], [nls_date_language] )
--

MAIN_ORDERS that finish(END_DATE) before DEC/11/2023 12:00:00 AM and NEED_DATE after JAN/16/2024

-- QUERY 4
select * from main_orders
where end_date <= date '2023-12-11'
and to_date(need_date,'YYYY-MM-DD HH24:MI:SS')> '16-JAN-2024' ;
--

this query shows MAINORDERS which are an end_date less than 2023-DEC-11 and need_date after 2024-JAN-16. But you have to note that two columns are into data types, but still we can convert data into DATE datatype from any other formats

Let’s use another way of Oracle SQL compare dates by using TO_DATE () function. In this query, try to write compare dates in those columns in different formats. See our query 5 bit hard compared to the earlier one

MAIN_ORDERS that starting(start_date) from 2 months and 12 days earlier than required date( need_date)

Now we are trying to compare the start_date and need_date columns, which are in DATE and VARCHAR type,s but still we can compare those with our requirements.

-- QUERY 5 
select * from main_orders
where start_date < add_months(to_date(need_date,'YYYY-MM-DD HH24:MI:SS')-12,-2) ;
--

If I explain a bit on query first we convert the VARCHAR type column date into DATE format and then reduce 12 days first then try to reduce 2 months by using ORACLE add_months function. After that compare the date we reduced is still less or not. Now The query returns output for MAIN_ORDERS that started (start_date) from 2 months and 12 days earlier than the required date( need_date).

Note: That is how we can use TO_DATE() function for Oracle SQL compare dates. We can use TO_DATE() function in your complex queries for filter records but that will miss your index on your table once fetching data from a table. They may have a full table scan for fetch records because of Use Untransformed Column Value.

4. TRUNC () Function

If you want to use the date without time part then you can use TRUNC () oracle function to cut only the date without the full date time. then you are able to compare only date values without time values. Sometimes we have to compare only the date but you have the complete date and time. in those scenarios use TRUNC() function for Oracle SQL compare dates.

MAIN_ORDERS that starting(start_date) today

-- QUERY 6
select * from main_orders
where trunc(start_date) < trunc(SYSDATE); -- My sysdate 6/10/2023 12:45:53 PM
--

Therefore, I don’t have records for starting today.

Trunc() Timestamp

You can use trunc() inbuilt Oracle function to convert timestamp value to date if required. Sometimes if there are two columns having DATE and TIMESTAMP values but you need to compare only the DATE part from TIMESTAMP you can trim by using TRUNC() function. See the small example for converting to date

--
SELECT CURRENT_TIMESTAMP,trunc(CURRENT_TIMESTAMP) from  dual 
--

5. Modify Session Parameters

Our next option is to alter session parameters. If you like you can modify current session parameters according to your wish. To view all modifiable parameters you have to run the below query.

-- QUERY 7
SELECT
    *
FROM
    v$parameter
WHERE
    isses_modifiable= 'TRUE'
--

From the list, we have to change the NLS_DATE_FORMAT parameter, which contains the default date format. Now check how to modify the current session date format by running the below statement.

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

Once you update the format for the current session, then table column values show according to our new format. Then you are able to use those formatted values for your next queries.

6. Use Intervals

Oracle has a lot of inbuilt functions for our SQL queries. Oracle SQL comparing dates by using an inbuilt function is easy and simple. For example, BETWEEN, MONTHS_BETWEEN, and ADD_MONTHS are used to compare dates between a specific time period Let’s write the below query to show all the MAIN_ORDERS ending date and the today’s current date gap between them is less than 6 months.

-- QUERY 8
SELECT * FROM MAIN_ORDERS ---- My sysdate 6/10/2023 12:45:53 PM
WHERE ABS(MONTHS_BETWEEN(end_date, sysdate)) <= 6
--

I hope now you know every possible way to compare dates in SQL Oracle. I have mentioned above all the successful ways but you may try the wrong ways to compare dates. I here also try to add some of the most common bad ways of comparing dates.

7. Wrong ways of comparing Dates

There can be a lot of wrong ways of comparing dates, but mentioning all possible bad ways is not going to mention. Below are common ways and errors returned by Oracle.

Compare incompatible Types

--
select * from main_orders
where start_date > need_date –start_date is DATE and need_date is VARCHAR
--

Error showing type is not compatible. You have to first convert both columns’ values into the same date format.

Incorrect default Date Format

--
select * from main_orders
where end_date <= 'DEC-01-2023';
--

Error saying that the received date format is not matching with the default date format of the current session. The default format is ‘DD-MMM-YYYY’ but we are going to compare on ‘MMM-DD-YYYY’

Incorrect Literal value

--
select * from main_orders
where end_date <= date '2023-DEC-01';
--

The error is due to a literal value not being expected. Date literal expects ‘YYYY-MM-DD’ type but we added month in a different format.

TO_DATE() function does not use the same date format for the column

--
select * from main_orders
where end_date <= date '2023-12-11'
and to_date(need_date,'YYY-MM-DD HH24:MI:SS')> '16-JAN-2024'
--

The error is because the string format is not correct according to the column date values. Here I added the format as ‘YYY-MM-DD HH24:MI:SS’ but it should be corrected as ‘YYYY-MM-DD HH24:MI:SS’.

Compare Dates Oracle SQL Performance

I here want to highlight some of the misuse of functions in Oracle. Sometimes we are writing complex queries, but those do not perform well. It’s because I write queries without thinking about performance. Table indexes are the most important in performance. Once you scan the table TO_DATE() or some other inbuilt function may avoid indexes rather use full table scan due to the optimizer being unable to use indexes with those functions.

Also, you can use the simplest condition first when Oracle SQL compare dates.

Summarization

In this article, consider comparing dates in Oracle SQL, and I try to summarize and highlight all possible comparing options and wrong options. Those options are works in SQL and PL/SQL on both. The date format and the default date type are most important for comparison, also create a table in the date data type if possible.

I hope this will solve your simple date comparison problem, feel free to comment in your Oracle questions, and also the good and bad things in this article. Thank you for reading and seeing our other related articles by clicking the reference links.