You just need basic date arithmetic, and the plain old date type.
CREATE TABLE doctor_visits (
doctor_id NUMBER NOT NULL,
in_time DATE NOT NULL,
out_time DATE NOT NULL
)
/
I presume you want to find doctors who were in the hospital at 10:00am, as opposed to doctors who visited exactly at 10AM
SELECT doctor_id FROM doctor_visits
where in_time < trunc(in_time) + NUMTODSINTERVAL(10,'HOUR')
AND out_time > trunc(in_time) + NUMTODSINTERVAL(10,'HOUR') ;
I haven’t considered the use case where in_time and out_time fall on different dates, i.e. say 11:00PM in and 2:00 am out.
OR IF you want to find a list of Doctors who visited the hospital exactly at 10:00AM then
SELECT doctor_id FROM doctor_visits where to_char(in_time,'HH:MI') = '10:00' ;
But that would be a very strange thing to query, as you really can’t be sure if some one visits exactly at that specific time.
1
solved insert only time in oracle [closed]