Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Check if a period of time is between two dates

Christian PitetMay 4 2021 — edited May 4 2021

Hi
I have a table containing dates.
I would like to make a sql query that checks if a period of time is contained in the table. It should return null if the period is not contained in the period of time.
For example in the table, date_debut (start-time) is to_date ('05/05/2021 09:00', 'DD/MM/YYYY HH24:MI'). date_fin (end-time) is ('05/05/2021 17:00', 'DD/MM/YYYY HH24:MI').
This is the data :

Insert into DT_AGENDA (PK,VEHICULE,DATE_DEBUT,DATE_FIN,CREE_PAR,RESERVE_POUR) values (975,221,to_date('05/05/2021 09:00:00','DD/MM/YYYY HH24:MI:SS'),to_date('05/05/2021 17:00:00','DD/MM/YYYY HH24:MI:SS'),'CHRISTIAN.PITET',null);

This is the table :

CREATE TABLE "DT_AGENDA" 
  (	"PK" NUMBER, 
	"VEHICULE" NUMBER NOT NULL ENABLE, 
	"DATE_DEBUT" DATE NOT NULL ENABLE, 
	"DATE_FIN" DATE NOT NULL ENABLE, 
	"CREE_PAR" VARCHAR2(128 BYTE), 
	"RESERVE_POUR" VARCHAR2(96 BYTE), 
	 CONSTRAINT "DT_AGENDA_PK" PRIMARY KEY ("PK"))

If I ask with a select if :P67_DATE_DEBUT (start-time) containing '05/05/2021 09:30' and :P67_DATE_FIN containing '05/05/2021 16:30', it should return one row.
If I ask with a select if :P67_DATE_DEBUT (start-time) containing '05/05/2021 06:30' and :P67_DATE_FIN containing '05/05/2021 07:30', it should return null.
I have tried this select with no good result :

select * from dt_agenda where ((to_date (:P67_DATE_DEBUT, 'DD/MM/YYYY HH24:MI') > date_debut and to_date (:P67_DATE_FIN, 'DD/MM/YYYY HH24:MI') > date_debut) and (to_date (:P67_DATE_DEBUT, 'DD/MM/YYYY HH24:MI') < date_fin and to_date (:P67_DATE_FIN, 'DD/MM/YYYY HH24:MI') < date_fin))
and vehicule=221;

Best regards.

Comments
Post Details
Added on May 4 2021
6 comments
4,242 views