SELECT column type DATE by HH24: mm: ss

Question

SELECT column type DATE by HH24: mm: ss

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

Hello, I have 2 DATE fields, depending on the snippet below:

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

And I need to query these fields using only the values for the HH24: mm: ss time, example: 10:00:00

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

I'm 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 was not efficient.

sql oracle date select

asked by anonymous 11.12.2018 / 19:11

2 answers

How to make an auto increment ID relative to another field in PostgreSQL "PostgreSQL"? [duplicate] How to use order by stop ordering the names of the months in Oracle

score 1 · Answer 1

I was able to resolve and bring up 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')

score 0 · Answer 2

Hello! I believe this example might help you:

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

Follow the link of the 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 got the information according to the equivalence of DATEPART (in this case the EXTRACT)). I hope I have helped.
Hugs,