Site icon IT Tutorial

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


 1,002 views last month,  2 views today

Exit mobile version