subtract time

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
rcampaneli
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 16
Joined: Mon, 31 Jul 2006 3:04 pm
Location: PR

Galera I use Oracle 8i and I have the following Select:

Select all

 
SELECT to_char(dat_abertura,'HH24:MI:SS')ABERTURA, 
To_char(DAT_FECHAMENTO, 'HH24:MI:SS')FECHAMENTO 
FROM CHM_CADASTRO WHERE STA_CHAMADO = 'Fechado' 
I would like to know how I can subtract the dat_abertura and the dat_fechamento?
Thanks
erthal
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 130
Joined: Mon, 22 Nov 2004 1:45 pm
Location: Niterói - RJ

Dude,

Take a look at this topic:

"Calculation of hours between two dates / hours"

The solution you are looking for ...
rcampaneli
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 16
Joined: Mon, 31 Jul 2006 3:04 pm
Location: PR

I made this following select

Select all

SELECT (dat_fechamento - dat_abertura)  
as Data  from CHM_CADASTRO WHERE STA_CHAMADO = 'Fechado'
Do you have any way to bring it with time format?
rcampaneli
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 16
Joined: Mon, 31 Jul 2006 3:04 pm
Location: PR

The two fields are in the Date Pharmat
erthal
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 130
Joined: Mon, 22 Nov 2004 1:45 pm
Location: Niterói - RJ

Pass the

Select all

to_date(,'hh24:mi:ss')
rcampaneli
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 16
Joined: Mon, 31 Jul 2006 3:04 pm
Location: PR

Would you like me how to do it, I can not do it ..
Thanks
Marlon Pasquali
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 28
Joined: Mon, 20 Mar 2006 3:40 pm
Location: Erechim - RS

This:

Select all

SELECT (to_date(dat_fechamento,'DD/MM/RRRR HH24:MI:SS') - to_date(dat_abertura,'DD/MM/RRRR HH24:MI:SS')) * 24 horas 
as Data from CHM_CADASTRO WHERE STA_CHAMADO = 'Fechado'
rcampaneli
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 16
Joined: Mon, 31 Jul 2006 3:04 pm
Location: PR

He brought the result with value 0 ..
Marlon Pasquali
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 28
Joined: Mon, 20 Mar 2006 3:40 pm
Location: Erechim - RS

is that the result is certainly giving smaller than 1 hour. See below the most complete example.

Select all

select trunc(( (dat_fechamento - dat_abertura) * 86400 / 3600)) ||':' || 
   trunc(mod( (dat_fechamento - dat_abertura) * 86400 , 3600 ) / 60 ) || ':'|| 
    trunc(mod ( mod ( (dat_fechamento - dat_abertura) * 86400, 3600 ), 60 )) Tempo 
as Data from CHM_CADASTRO WHERE STA_CHAMADO = 'Fechado'
rcampaneli
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 16
Joined: Mon, 31 Jul 2006 3:04 pm
Location: PR

Dude, that's right ... Thank you very much for everyone.

Thanks
FMMS
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 3
Joined: Wed, 24 Oct 2018 9:01 am

Marlon Pasquali wrote: is that the result is certainly giving smaller than 1 hour. See below the most complete example.

Select all

select trunc(( (dat_fechamento - dat_abertura) * 86400 / 3600)) ||':' || 
   trunc(mod( (dat_fechamento - dat_abertura) * 86400 , 3600 ) / 60 ) || ':'|| 
    trunc(mod ( mod ( (dat_fechamento - dat_abertura) * 86400, 3600 ), 60 )) Tempo 
as Data from CHM_CADASTRO WHERE STA_CHAMADO = 'Fechado'
/ quote]

follows ex:

Select all

SELECT  ap.nr_atendimento, 
        ap.DT_ALTA, 
        ap.DT_ENTRADA, 
         
            (SELECT TRUNC(( (apu.dt_saida_unidade - apu.dt_entrada_unidade) * 86400 / 3600)) || ':' || 
                    TRUNC(MOD( (apu.dt_saida_unidade - apu.dt_entrada_unidade) * 86400, 3600) / 60) || ':' || 
                    TRUNC(MOD ( MOD ( (apu.dt_saida_unidade - apu.dt_entrada_unidade) * 86400, 3600),60)) 
                     
            FROM atend_paciente_unidade apu 
                [b]WHERE apu.cd_setor_atendimento = 129 --Aqui pode ter duas ou mais datas, aí precisaria somar--[/b] 
                    AND apu.nr_atendimento = ap.nr_atendimento) unid_inter_3_andar 
 
FROM atendimento_paciente_v ap, 
     conta_paciente c, 
     cirurgia r 
 
WHERE ap.nr_atendimento = c.nr_atendimento 
   and ap.NR_ATENDIMENTO = r.nr_atendimento(+) 
   AND ap.cd_estabelecimento = 1 --:cd_estabelecimento 
   AND ap.dt_entrada between '20/10/2018' AND '24/10/2018' --:dt_inicial and :dt_final + 86399 / 86400 
   AND exists (select 1 
          from conta_paciente 
         where nr_atendimento = ap.NR_ATENDIMENTO) 
   --AND ap.NR_ATENDIMENTO = 1844338 
 
GROUP BY ap.nr_atendimento,ap.DT_ALTA,ap.DT_ENTRADA 
 
ORDER BY ap.DT_ENTRADA;
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 14 guests