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.