SELECT DATE column for HH24:mm:ss

Question

Hello, I have 2 fields of type DATE, as the snippet below:

  ...hr_inicio                 DATE         NOT NULL,
     hr_fim                    DATE         NOT NULL, ...

And I am in need of consulting these fields using only the values relating to time HH24:mm:ss, example: 10:00:00

In this way I would like to carry out my select by passing the time, minute and second as parameters of the Where clause. To bring all dates that have the start time 10:00:00 and end time 12:00:00 as shown in the image below.

I am using Oracle SQL

---

PS: I’ve tried using the following way:

SELECT * FROM TABELA WHERE 
HR_INICIO = TO_DATE('10:00:00','hh24:mi:ss') AND
HR_FIM    = TO_DATE('12:00:00','hh24:mi:ss')

But it wasn’t efficient.

Answer 1

I was able to resolve and bring the records using the following code:

SELECT * FROM TABELA
  WHERE TO_char(HR_INICIO,'hh24:mi:ss') = TO_char(TO_DATE('10:00:00','hh24:mi:ss'),'hh24:mi:ss')
  AND TO_char(HR_FIM,'hh24:mi:ss') = TO_char(TO_DATE('13:00:00','hh24:mi:ss'),'hh24:mi:ss')

Answer 2

Hello! I believe this example can help you:

SELECT last_name, employee_id, hire_date
  FROM employees
 WHERE EXTRACT(HOUR FROM
TO_DATE(hire_date, 'DD-MON-RR')) = 10

Follows link of documentation.
In SQL SERVER I do:

SELECT *
  FROM @TABLE
 WHERE DATEPART(HOUR, DataEntrada) = 10 --Para hora inicial 10h
   AND DATEPART(HOUR, DataSaida) = 18 --Para hora final 18h

I obtained the information according to the equivalence of DATEPART (in this case EXTRACT)).
I hope I’ve helped.
Hugs,