Oracle SQL Date Comparison

I will explain Oracle SQL Date Comparison in this post.


Oracle SQL Date Comparison

SQL Date comparison is most used statement for DBA or developers. If you will compare any column with a DATE format, related column and data should be DATE datatype, namely SQL date comparison should be between DATE to DATE format as follows.


where date_column <> to_date('2020-01-01','YYYY-MM-DD');

where to_char(begin_interval_time,'DD-MON-YYYY')='10-JUN-2021'

EXTRACT(HOUR FROM begin_interval_time) between 8 and 10;



Compare dates in Oracle 

I will query the dba_hist_active_sess_history according to sample_time between ’07/04/2021 07:30:00′ and ’08/04/2021 15:10:02′ as follows.

for example;

select v.sql_text,v.sql_fulltext,sub.* from v$sql v,
(select sample_time,s.sql_id sql_id, session_state, blocking_session,
owner||'.'||object_name||':'||nvl(subobject_name,'-') obj_name,s.program,s.module,s.machine
from dba_hist_active_sess_history s, dba_objects o
where sample_time between
to_date('07/04/2021 07:30:00','DD/MM/YYYY HH24:MI:SS')
to_date('08/04/2021 15:10:02','DD/MM/YYYY HH24:MI:SS');

select lastname
from employees
where date_hired > to_date('01-DEC-20','DD-MON-YY');



select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='10-JUN-2021'
and EXTRACT(HOUR FROM begin_interval_time) between 8 and 10;





Oracle can implicitly convert ’24-JUL-21′ to a DATE value using the default date format ‘DD-MON-YY’ as follows.

SELECT last_name
FROM employees
WHERE hire_date = '24-JUL-21';



You should read the following post to learn more details about TO_DATE function.

Oracle TO_DATE | Convert Datetime to Date





Do you want to learn Oracle SQL for Beginners, then read the following articles.

Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course


About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *