Help Select which brings Hours

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Tue, 08 May 2007 11:33 am

Dear friends I am with this select below: but when the second surpasses 2 decimal places thus ### how can I fix this???

Accept any help possible because I thought almost everything lol ....

Hugs friends ...:D
Code: Select all
SELECT abs(Ltrim(To_Char(Trunc(((TO_DATE('01/12/2006 11:05:45','DD/MM/YYYY HH24:MI:SS')
- TO_DATE('02/12/2006 13:25:40','DD/MM/YYYY HH24:MI:SS')))*24),'999999999999999'),' '))
         ||':'||
         Ltrim(To_Char(abs(Round((((TO_DATE('01/12/2006 11:05:45','DD/MM/YYYY HH24:MI:SS') - TO_DATE('02/12/2006 13:25:40','DD/MM/YYYY HH24:MI:SS')))*24
         - Trunc(((TO_DATE('01/12/2006 11:05:45','DD/MM/YYYY HH24:MI:SS')
        - TO_DATE('02/12/2006 13:25:40','DD/MM/YYYY HH24:MI:SS')))*24))*60)),'00'),' ')
        ||':'||
         Ltrim(To_Char(abs(Round((((TO_DATE('01/12/2006 11:05:45','DD/MM/YYYY HH24:MI:SS') - TO_DATE('02/12/2006 13:25:40','DD/MM/YYYY HH24:MI:SS')))*24
         - Trunc(((TO_DATE('01/12/2006 11:05:45','DD/MM/YYYY HH24:MI:SS')
        - TO_DATE('02/12/2006 13:25:40','DD/MM/YYYY HH24:MI:SS')))*24))*60))*360,'00'),' ')
        horas

        from dual
madmax
Location: São Paulo

Poston Tue, 08 May 2007 1:49 pm

I know that doesn't answer your question, but there's a similar query that I did a while back:
Code: Select all
select horas||':'||minutos||':'||segundos
from
(
    select lpad(trunc(           abs(data1-data2) * 24          )       , 2, '0') horas
         , lpad(trunc((mod(trunc(abs(data1-data2) * 24 * 60     ), 60))), 2, '0') minutos
         , lpad(trunc((mod(round(abs(data1-data2) * 24 * 60 * 60), 60))), 2, '0') segundos
    from
    (
        select to_date('01/12/2006 11:05:45','DD/MM/YYYY HH24:MI:SS') data1
             , to_date('02/12/2006 13:25:40','DD/MM/YYYY HH24:MI:SS') data2
        from dual
    )
);
rogenaro
Location: Londrina - PR

Poston Tue, 08 May 2007 4:56 pm

Dear friend Your Rogenaro select fell like a glove lol ...

Very sheltered friend.. :-o
madmax
Location: São Paulo

Poston Tue, 15 May 2007 11:55 am

Look, since the subject is that, for a while the vindalencio posted a select that selected multiple fields that calculate several forms, dates and times:
Code: Select all
SELECT SYSDATE DATA_ATUAL,
               ADD_MONTHS(SYSDATE,-1) MENOS_UM_MES,
               ADD_MONTHS(SYSDATE,1) MAIS_UM_MES,
               LAST_DAY(SYSDATE) ULTIMO_DIA_MES,
               TRUNC(SYSDATE,'MONTH') PRIMEIRO_DIA_MES,
               MONTHS_BETWEEN(SYSDATE,'01-JAN-2001') QTD_MES,
               FLOOR(MONTHS_BETWEEN(SYSDATE,'01-JAN-2001')) ARREDONDA_ABAIXO,
               CEIL(MONTHS_BETWEEN(SYSDATE,'01-JAN-2001')) ARREDONDA_ACIMA,
               ABS(MONTHS_BETWEEN(SYSDATE,'01-JAN-2001')) VALOR_ABSOLUTO,
               ROUND((MONTHS_BETWEEN(SYSDATE,'01-JAN-2001')),3) ARREDONDA,
               TRUNC(SYSDATE,'year') PRIMEIRO_DIA_ANO,
               to_char(SYSDATE,'dd " de " FMMONTH " de " YYYY','nls_date_language=portuguese')DIAMES_EXTENSO,
               to_char(SYSDATE,'FMMONTH " DE " YYYY','nls_date_language=portuguese') MES_EXTENSO,
               TO_CHAR(SYSDATE,'HH24:MI') HORA_MINUTO,
               to_char(sysdate,'FMMonth','nls_date_language=portuguese') mes_corrente,
               to_date(lpad(to_char(1234),4,'0'),'hh24mi') dias_data,
               Floor(floor(months_between(SYSDATE,to_date('11/02/1982'))) / 12) IDADE,
               decode(TO_NUMBER(TO_CHAR(SYSDATE,'D')),2,'segunda-feira',
                                                      3,'terça-feira',
                                                      4,'quarta-feira',
                                                      5,'quinta-feira',
                                                      6,'sexta-feira',
                                                      7,'sabado',
                                                      1,'domingo') dia_da_semana
  FROM DUAL
:D
Toad
Location: Seattle, WA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek

Poston Wed, 24 Oct 2018 10:00 am

Topic under translation. Come back later. (This is an automatic message)
FMMS

Poston Wed, 24 Oct 2018 10:31 am

Topic under translation. Come back later. (This is an automatic message)
FMMS

Poston Wed, 24 Oct 2018 1:32 pm

Topic under translation. Come back later. (This is an automatic message)
Toad
Location: Seattle, WA

Matheus H. Gonçalves
www.toad.com.br
www.twitter.com/toadgeek

Poston Sun, 03 Feb 2019 3:26 pm

Topic under translation. Come back later. (This is an automatic message)
Renato Menezes Viana
Location: Rio de Janeiro - RJ


  • See also
    Replies
    Views
    Last Post


        Return to SQL

        Who is online

        Users browsing this forum: No registered users and 3 guests