Oracle where condition doing the filtering data in a table according to condition but date in where clause oracle is bit special, because of bad performance. Oracle indexes like range indexes or unique indexes are used for better performance.
However, adding an index like date column is not the best solution for a filter by date in where clause oracle. Because in such cases it can slow down the DML operation like INSERT and UPDATE. Also, sometimes dates can be saved with time and without time part also sometimes saved as a VARCHAR in a different format.
VARCHAR columns are not able to be used directly with the date parameter. Compare dates in where the condition is highly used in queries, so below showing ways of usability of date in where clause oracle.
Date in Where Clause Oracle Examples
If you really need performance on date VARCHAR column that is formatted on query then again column not taking index directly added to the column then you have to create function based index including format.
-- Syntax SELECT * FROM TABLE WHERE DATE_COLUMN = :Date_value_; SELECT * FROM TABLE WHERE DATE_COLUMN <Date_value_; SELECT * FROM TABLE WHERE DATE_COLUMN BETWEEN :Start_Date_value_ and :Start_Date_value_ ; --
1. Comparing operators
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 date save in the table as VARCHAR in that time we have to convert VARCHAR into DATE type otherwise 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' ; --
Date time format use different type of format as a VARCHAR column there are CHAR values use to convert save to values to date type value. Below are the values and the description for those conversions.
Examples of date format filters in where clause
--
SELECT *
FROM EMPLOYEE
WHERE TRUNC(TIME_CREATED) = TO_DATE('26/JAN/2024','dd/mon/yyyy')
--Convert VARCHAR column to date with full format
SELECT TO_DATE('14-06-2022 20:30','dd-MM-yyyy HH24:MI') FROM dual
--If you want to write it a nice way with BETWEEN the use below code.
--Similar code able to write with logical operators.
a.need_date BETWEEN
TO_DATE('14-06-2022 18:30','dd-MM-yyyy HH24:MI') AND
TO_DATE('25-06-2022 18:50','dd-MM-yyyy HH24:MI')
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 --
Date Range filter in Oracle SQL
If you are concerned about the time component on the date, then use trunc() to remove the unnecessary parts from date values. The below example shows the date in where clause oracle but in a specific range
--select *
from dual
WHERE trunc(sysdate) BETWEEN TO_DATE('28/02/2024', 'DD/MM/YYYY') AND
TO_DATE('20/06/2024', 'DD/MM/YYYY');
--Oracle Filter by date without Time
DATE keyword in Oracle as literal as we explain in above, uses logical operators and BETWEEN keywords to filter the dataset. If you have an index on date, will give better performance
--WHERE your_date >= TO_DATE('2020-08-04', 'YYYY-MM-DD')
AND your_date < TO_DATE('2020-08-24', 'YYYY-MM-DD')
--or BETWEEN:
WHERE your_date BETWEEN TO_DATE('2020-08-01', 'YYYY-MM-DD')
AND TO_DATE('2020-08-13 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
--
TRUNC parameter removes hours, minutes, and seconds from a DATE. If performance is really a problem then put a Function-Based Index on that column
-- CREATE INDEX trunc_date_idx ON t1(TRUNC(your_date)); --
Oracle Filter by Timestamp
If you want to filter by timestamp there are two simple functions to use. To_timestamp and To_date able to convert strings to time stamps or date including seconds. If a column is type date then no need to convert.
to_timestamp()
You need to use to_timestamp() to convert your string to a proper timestamp value:
--
to_timestamp('12-01-2025 21:24:00', 'dd-mm-yyyy hh24:mi:ss')
--to_date()
If your column is of type DATE (which also supports seconds), you need to use to_date()
--
to_date('12-01-2025 21:24:00', 'dd-mm-yyyy hh24:mi:ss')
--Example
To get this into a where condition use the following:
--
select *
from TableA
where startdate >= to_timestamp('12-01-2025 21:24:00', 'dd-mm-yyyy hh24:mi:ss')
and startdate <= to_timestamp('12-01-2025 21:25:33', 'dd-mm-yyyy hh24:mi:ss')
--Where condition filters data according to timestamp value. However, here, we can have performance issues if there are millions of records due to full table scans.
