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. Else 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 modify date format parameters that use in the session.

Content available on

1. Comparative operators
2.  Date literals
3. TO_DATE () function
4. TRUNC () Function
5. Modify Session Parameters
6. Use Intervals
7. 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 to compare dates.

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

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

1. 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.

2. 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 support 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'
--

1. 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 to compare dates. This supports any format which can be converted into DATE. Some times date save in the table as VARCHAR in that time we have to convert VARCHAR into DATE type otherwise raise a error when running.

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

1. 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

2. 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 those are in DATE and VARCHAR types 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.

1. 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() inbuild 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 convert 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 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 are 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 date in SQL Oracle. I here above mention 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 mention all possible bad ways are 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';
--

Error is due to literal value is not expected. Date literal expect ‘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'
--

Error is because of string format 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 write queries without thinking about performance. Table indexes are the most important in performance. Once you scan the table TO_DATE() or some other inbuild 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 most simple 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 PLSQL on both. The date format and the default date type is most important for comparison also create a table in 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.

Reference

https://www.devart.com/dbforge/oracle/studio/comparing-dates-oracle.html

All SQL Related posts https://ennicode.com/category/sql/