Time range between ORACLE SQL dates

Asked

Viewed 335 times

-1

Good afternoon guys, I have a question in oracle SQL in which to solve I wanted to get the following information:

Before, I have this query:

SELECT  cd_atendimento,ds_atendimento,cd_valor,hr_atendimento
FROM  atendimento WHERE cd_atendimento IN
(
  5587064,
  5584065,
  5587654,
  4568985,
  5587062,
  5584061,
  5587659,
  4568910
)
AND hr_atendimento BETWEEN TO_DATE('01/11/2020 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
AND TO_DATE('11/11/2020 05:59:00', 'DD/MM/YYYY HH24:MI:SS')
AND cd_atendimento IS NOT NULL

I wanted him to return, All calls From day 01 until day 11 within hours: 00:00 until 05:59, It would be a filter by "DATE AND TIME Obviously", ie he would have to return me all calls performed between this hour of each day.

ex of desired return :

Dia 01 de 00:00 as 05:59 : atendimentos: 5587064,
  5584065,
  5587654

Dia 02 de 00:00 as 05:59: atendimentos:5584061,
  5587659,
  4568910

and so on...

Can you help me? , because the way it is it will return me from the 1st to 00:00 until the 11th until 05:59, which is not what I need :(

sql oracle date

1 answer

0


Separe data e hora
SELECT cd_atendimento,
           ds_atendimento,
           cd_valor,
           hr_atendimento 
    FROM atendimento 
    WHERE cd_atendimento IN ( 5587064, 5584065, 5587654, 4568985, 5587062, 5584061, 5587659, 4568910 ) 
    AND trunc(hr_atendimento) BETWEEN TO_DATE('01/11/2020', 'DD/MM/YYYY') AND 
                                      TO_DATE('11/11/2020', 'DD/MM/YYYY') 
    AND to_char(hr_atendimento,'hh24mi') BETWEEN '0000' and '0559'  
    AND cd_atendimento IS NOT NULL

Browser other questions tagged sql oracle date

You are not signed in. Login or sign up in order to post.