none
Erro para converter varchar to time RRS feed

  • Pergunta

  • Ola, 

    tenho campo varchar na tabela = 215:43:29:000.

    preciso somar em tempo todos os registros por funcionário, e me apresenta erro.

    CONVERT(time, [horafuncionario],108), e cast não resolveu. Erro de conversao

    Após isso tenho que fazer uma subtração para analisar o saldo.

    segunda-feira, 4 de janeiro de 2021 17:54

Respostas

  • Tente separar os valores da coluna em 2 partes, uma em minutos para a soma da parte das horas e minutos e outra em milisegundos para a soma da parte dos segundos e mliisegundos, para trabalhar com unidades de valor menor e dessa forma tentar evitar o estouro:

    select 
        cast(PARSENAME(REPLACE(horafuncionario, ':', '.'), 4) as int) * 60 +
        cast(PARSENAME(REPLACE(horafuncionario, ':', '.'), 3) as int) as minutos,
        cast(PARSENAME(REPLACE(horafuncionario, ':', '.'), 2) as int) * 1000 +
        cast(PARSENAME(REPLACE(horafuncionario, ':', '.'), 1) as int) as milisegundos

    E no final para pegar o resultado das 2 partes e converter para Datetime:

    select
        dateadd(minute, minutos, dateadd(millisecond, milisegundos, 0))

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    • Marcado como Resposta claudiof terça-feira, 5 de janeiro de 2021 22:14
    terça-feira, 5 de janeiro de 2021 20:32
  • Alguns bateram nas não todas

    (No column name)                           conferencia
    1900-01-11 06:30:51.000               246:30:51:000
    1900-01-01 00:23:54.000                   0:23:54:000

    quando for mais 24 horas tem que somar na hora, e como vou tirar o 1900-01-01, 
    pois já esta com um select e um subselect e ainda somar o resultado deste exemplo 246:54:51:000


    • Editado claudiof terça-feira, 5 de janeiro de 2021 22:01
    • Marcado como Resposta claudiof terça-feira, 5 de janeiro de 2021 22:14
    terça-feira, 5 de janeiro de 2021 21:59
  • Para fazer a conversão para aquele formato é necessário passar o valor com o tipo de dados DateTime ou semelhante, e para obter o resultado dos cálculos nesse tipo você pode utilizar aquele trecho com o DateAdd:

    dateadd(minute, minutos, dateadd(millisecond, milisegundos, 0))

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    • Marcado como Resposta claudiof quarta-feira, 6 de janeiro de 2021 19:45
    quarta-feira, 6 de janeiro de 2021 18:33
  • resolvi agora preciso somar 

    **Operand data type varchar is invalid for sum operator.


    • Editado claudiof quinta-feira, 7 de janeiro de 2021 12:01
    • Marcado como Resposta claudiof sexta-feira, 8 de janeiro de 2021 10:54
    quarta-feira, 6 de janeiro de 2021 19:45
  • Pelo que vi essa sua consulta não está somando conforme foi sugerido anteriormente.

    Segue um exemplo para testes:

    with
        CTE_Soma as
        (
            SELECT TOP 1000
                [nome],
                sum
                    (cast(PARSENAME(REPLACE([horatot], ':', '.'), 4) as int) * 60 +
                     cast(PARSENAME(REPLACE([horatot], ':', '.'), 3) as int)) as minutos,
                sum
                    (cast(PARSENAME(REPLACE([horatot], ':', '.'), 2) as int) * 1000 +
                     cast(PARSENAME(REPLACE([horatot], ':', '.'), 1) as int)) as milisegundos
            FROM tabela
            group by
                [nome]
        ),
    
        CTE_DateTime as
        (
            SELECT 
                nome
                dateadd(minute, minutos, dateadd(millisecond, milisegundos, 0)) as tempot
            from CTE_DateTime
        )
    
    select
        nome,
        convert(varchar(10), DATEDIFF(DAY, 0, tempot) * 24 + DATEPART(HOUR, tempot)) +
        RIGHT(convert(char(12), tempot, 114), 10)
    FROM CTE_DateTime
    

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    • Marcado como Resposta claudiof quinta-feira, 7 de janeiro de 2021 18:30
    • Não Marcado como Resposta claudiof quinta-feira, 7 de janeiro de 2021 18:30
    • Marcado como Resposta claudiof quinta-feira, 7 de janeiro de 2021 18:31
    quinta-feira, 7 de janeiro de 2021 17:29
  • Segue outro exemplo para testes onde é adicionada uma CTE antes da conversão para DateTime para fazer o calculo da média:

    with
        CTE_Soma as
        (
            SELECT TOP 1000
                [nome],
                sum
                    (cast(PARSENAME(REPLACE([horatot], ':', '.'), 4) as int) * 60 +
                     cast(PARSENAME(REPLACE([horatot], ':', '.'), 3) as int)) as minutos,
                sum
                    (cast(PARSENAME(REPLACE([horatot], ':', '.'), 2) as int) * 1000 +
                     cast(PARSENAME(REPLACE([horatot], ':', '.'), 1) as int)) as milisegundos
                -- ... 
            FROM tabela
            group by
                [nome]
        ),
    
        CTE_Media as
        (
            select
                Nome,
                (minutosi - minutosp) / qtd as minutos,
                (milisegundosi - milisegundosp) / qtd as milisegundos
                -- ...            
            from CTE_Soma
        ),
    
        CTE_DateTime as
        (
            SELECT 
                nome
                dateadd(minute, minutos, dateadd(millisecond, milisegundos, 0)) as tempot
            from CTE_Media
        )
    
    select
        nome,
        convert(varchar(10), DATEDIFF(DAY, 0, tempot) * 24 + DATEPART(HOUR, tempot)) +
        RIGHT(convert(char(12), tempot, 114), 10)
    FROM CTE_DateTime

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br


    • Editado gapimex quinta-feira, 7 de janeiro de 2021 19:06
    • Marcado como Resposta claudiof quinta-feira, 7 de janeiro de 2021 20:14
    quinta-feira, 7 de janeiro de 2021 19:05

Todas as Respostas

  • Boa tarde,

    Segue uma sugestão para converter o valor varchar em milisegundos com o tipo inteiro para somar e subtrair outros valores:

    select
        cast(PARSENAME(REPLACE(horafuncionario, ':', '.'), 4) as int) * 3600000 +
        cast(PARSENAME(REPLACE(horafuncionario, ':', '.'), 3) as int) * 60000 +
        cast(PARSENAME(REPLACE(horafuncionario, ':', '.'), 2) as int) * 1000 +
        cast(PARSENAME(REPLACE(horafuncionario, ':', '.'), 1) as int);

    E uma sugestão para pegar o resultado final e converter para datetime ou semelhante:

    select 
        dateadd(millisecond, resultadofinal, 0)
    

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    segunda-feira, 4 de janeiro de 2021 18:47
  • Acho que tem uma vírgula sobrando depois do Select da CTE e antes da coluna horatotalincidente, e no Select da parte final é selecionada a coluna _signatario que não existe na CTE. 

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    terça-feira, 5 de janeiro de 2021 12:14
  • Concordo mas não soma,

    with CTE_Calc as
    (
    SELECT 
          [horatotalincidente]
        --,sum(replace([horatotalincidente],':',''))


     ,cast(PARSENAME(REPLACE(horatotalincidente, ':', '.'), 4) as int) * 3600000 +
        cast(PARSENAME(REPLACE(horatotalincidente, ':', '.'), 3) as int) * 60000 +
        cast(PARSENAME(REPLACE(horatotalincidente, ':', '.'), 2) as int) * 1000 +
        cast(PARSENAME(REPLACE(horatotalincidente, ':', '.'), 1) as int) as calculo
     --sum(calculo)



      FROM horários)

      SELECT sum(calculo)

                        
     
       
     FROM CTE_Calc
     group by calculo


    • Editado claudiof terça-feira, 5 de janeiro de 2021 15:56
    terça-feira, 5 de janeiro de 2021 13:57
  • Se você colocar a coluna Calculo no Group By as linhas com valores diferentes nessa coluna não serão agrupadas.

    Assinatura: http://www.imoveisemexposicao.com.br

    terça-feira, 5 de janeiro de 2021 17:59
  • Msg 8115, Level 16, State 2, Line 6
    Arithmetic overflow error converting expression to data type int.
    terça-feira, 5 de janeiro de 2021 18:04
  • Tente separar os valores da coluna em 2 partes, uma em minutos para a soma da parte das horas e minutos e outra em milisegundos para a soma da parte dos segundos e mliisegundos, para trabalhar com unidades de valor menor e dessa forma tentar evitar o estouro:

    select 
        cast(PARSENAME(REPLACE(horafuncionario, ':', '.'), 4) as int) * 60 +
        cast(PARSENAME(REPLACE(horafuncionario, ':', '.'), 3) as int) as minutos,
        cast(PARSENAME(REPLACE(horafuncionario, ':', '.'), 2) as int) * 1000 +
        cast(PARSENAME(REPLACE(horafuncionario, ':', '.'), 1) as int) as milisegundos

    E no final para pegar o resultado das 2 partes e converter para Datetime:

    select
        dateadd(minute, minutos, dateadd(millisecond, milisegundos, 0))

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    • Marcado como Resposta claudiof terça-feira, 5 de janeiro de 2021 22:14
    terça-feira, 5 de janeiro de 2021 20:32
  • Alguns bateram nas não todas

    (No column name)                           conferencia
    1900-01-11 06:30:51.000               246:30:51:000
    1900-01-01 00:23:54.000                   0:23:54:000

    quando for mais 24 horas tem que somar na hora, e como vou tirar o 1900-01-01, 
    pois já esta com um select e um subselect e ainda somar o resultado deste exemplo 246:54:51:000


    • Editado claudiof terça-feira, 5 de janeiro de 2021 22:01
    • Marcado como Resposta claudiof terça-feira, 5 de janeiro de 2021 22:14
    terça-feira, 5 de janeiro de 2021 21:59
  • Claudiof,

    O retorno do valor 1900-01-01 esta ocorrendo provavelmente por que o valor da data esta fora da faixa ou não foi encontrado.....

    Será que o uso das funções IsNull() ou Coalesce() não poderiam te ajudar?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 6 de janeiro de 2021 11:14
  • não resolveu o problemas com as duas funções

    quarta-feira, 6 de janeiro de 2021 11:59
  • Não entendi, acho que o resultado da coluna conferencia está correto nos 2 exemplos.

    Assinatura: http://www.imoveisemexposicao.com.br

    quarta-feira, 6 de janeiro de 2021 12:44
  • Sim, a do resultado 2 esta correto, quando for menos de 24 horas fica correto, quando é acima 25 ele me tira fica como errado.

    (No column name)                           conferencia
    1900-01-11 06:30:51.000               246:30:51:000

    quarta-feira, 6 de janeiro de 2021 13:05
  • Acho que o resultado acima está correto, pois 10 dias x 24 = 240 horas + 6 horas = 246 horas, e a parte dos minutos, segundos e milésimos de segundo segue inalterada após o calculo.

    Assinatura: http://www.imoveisemexposicao.com.br

    quarta-feira, 6 de janeiro de 2021 13:45
  • certo mas como vou calcular para chegar na coluna conferencia.
    quarta-feira, 6 de janeiro de 2021 13:57
  • Conversion failed when converting the varchar value '246:30:51:000' to data type int.

    with CTE_Calc as
    (
    SELECT TOP 100
      
        cast(PARSENAME(REPLACE([horatotal], ':', '.'), 4) as int) *60 +
        cast(PARSENAME(REPLACE([horatotal], ':', '.'), 3) as int) as minutos,
        cast(PARSENAME(REPLACE([horatotal], ':', '.'), 2) as int) * 1000 +
        cast(PARSENAME(REPLACE([horatotal], ':', '.'), 1) as int) as milisegundos
         
      FROM tabela)
        SELECT
       dateadd(minute, minutos, dateadd(millisecond, milisegundos, 0))
      ,[horatotal] as conferencia
      ,convert
            (varchar(10),
             DATEDIFF (DAY, 0, iif( [horatotal]<>null,0,[horatotal])* 24 +
             DATEPART(HOUR,  [horatotal])) + RIGHT
            (convert(char(12), iif( [horatotal]<>null,0,[horatotal],114), 10) as total
     FROM CTE_Calc
     


    quarta-feira, 6 de janeiro de 2021 16:32
  • Para fazer a conversão para aquele formato é necessário passar o valor com o tipo de dados DateTime ou semelhante, e para obter o resultado dos cálculos nesse tipo você pode utilizar aquele trecho com o DateAdd:

    dateadd(minute, minutos, dateadd(millisecond, milisegundos, 0))

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    • Marcado como Resposta claudiof quarta-feira, 6 de janeiro de 2021 19:45
    quarta-feira, 6 de janeiro de 2021 18:33
  • resolvi agora preciso somar 

    **Operand data type varchar is invalid for sum operator.


    • Editado claudiof quinta-feira, 7 de janeiro de 2021 12:01
    • Marcado como Resposta claudiof sexta-feira, 8 de janeiro de 2021 10:54
    quarta-feira, 6 de janeiro de 2021 19:45
  • Esse erro não deveria ocorrer, as colunas a serem somadas seriam a coluna minutos e milisegundos, que são do tipo integer e podem ser somadas.

    Acho melhor você postar a sua consulta para analise.


    Assinatura: http://www.imoveisemexposicao.com.br

    quinta-feira, 7 de janeiro de 2021 13:56
  • não somou

    conferencia     total
    0:23:54:000    0:23:54:000
    246:30:51:000 246:30:51:000

    with CTE_Calc as
    (
    SELECT TOP 1000
          [horatot]
        --,sum(replace([horatot],':',''))
            ,[nome]
       ,cast(PARSENAME(REPLACE([horatot], ':', '.'), 4) as int) *60 +
        cast(PARSENAME(REPLACE([horatot], ':', '.'), 3) as int) as minutos,
        cast(PARSENAME(REPLACE([horatot], ':', '.'), 2) as int) * 1000 +
        cast(PARSENAME(REPLACE([horatot], ':', '.'), 1) as int) as milisegundos

      FROM tabela )
      SELECT nome
       --dateadd(minute, minutos, dateadd(millisecond, milisegundos, 0)) as tempot
      ,[horatot] as conferencia
      ,convert
            (varchar(10),
             DATEDIFF
               (DAY,
                0,
                iif( [horatot]<>null,0, dateadd(minute, minutos, dateadd(millisecond, milisegundos, 0)))
       ) *
             24 +
             DATEPART
               (HOUR,
                --iif( [horatot]<>null,0,[horatot])
     dateadd(minute, minutos, dateadd(millisecond, milisegundos, 0))
       )) +
          RIGHT
            (convert
               (char(12),
              iif( [horatot]<>null,0, dateadd(minute, minutos, dateadd(millisecond, milisegundos, 0)))
       ,
               114),
             10) as total

     

      FROM CTE_Calc
     group by nome,minutos,milisegundos,[horatot]


    quinta-feira, 7 de janeiro de 2021 14:38
  • Pelo que vi essa sua consulta não está somando conforme foi sugerido anteriormente.

    Segue um exemplo para testes:

    with
        CTE_Soma as
        (
            SELECT TOP 1000
                [nome],
                sum
                    (cast(PARSENAME(REPLACE([horatot], ':', '.'), 4) as int) * 60 +
                     cast(PARSENAME(REPLACE([horatot], ':', '.'), 3) as int)) as minutos,
                sum
                    (cast(PARSENAME(REPLACE([horatot], ':', '.'), 2) as int) * 1000 +
                     cast(PARSENAME(REPLACE([horatot], ':', '.'), 1) as int)) as milisegundos
            FROM tabela
            group by
                [nome]
        ),
    
        CTE_DateTime as
        (
            SELECT 
                nome
                dateadd(minute, minutos, dateadd(millisecond, milisegundos, 0)) as tempot
            from CTE_DateTime
        )
    
    select
        nome,
        convert(varchar(10), DATEDIFF(DAY, 0, tempot) * 24 + DATEPART(HOUR, tempot)) +
        RIGHT(convert(char(12), tempot, 114), 10)
    FROM CTE_DateTime
    

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    • Marcado como Resposta claudiof quinta-feira, 7 de janeiro de 2021 18:30
    • Não Marcado como Resposta claudiof quinta-feira, 7 de janeiro de 2021 18:30
    • Marcado como Resposta claudiof quinta-feira, 7 de janeiro de 2021 18:31
    quinta-feira, 7 de janeiro de 2021 17:29
  • Calculados a soma dos 10 campos, 

    mas tenho que fazer uma média de um campo (

    **erro 
    Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

    )

    adicionado dentro do CTE_DateTime

    ((dateadd(minute, minutosi, dateadd(millisecond, milisegundosi, 0))) - (dateadd(minute, minutosp, dateadd(millisecond, milisegundosp, 0))) ) / [qtd] as med


    • Editado claudiof quinta-feira, 7 de janeiro de 2021 18:33
    quinta-feira, 7 de janeiro de 2021 18:32
  • assim 

    não fecha bate os minutos 

    convert(varchar(10), DATEDIFF(DAY, 0, tempotx) * 24 /qtd+ DATEPART(HOUR, tempotx)/qtd) +
        RIGHT(convert(char(12), tempotx, 114), 10)



    • Editado claudiof quinta-feira, 7 de janeiro de 2021 18:52
    quinta-feira, 7 de janeiro de 2021 18:43
  • Segue outro exemplo para testes onde é adicionada uma CTE antes da conversão para DateTime para fazer o calculo da média:

    with
        CTE_Soma as
        (
            SELECT TOP 1000
                [nome],
                sum
                    (cast(PARSENAME(REPLACE([horatot], ':', '.'), 4) as int) * 60 +
                     cast(PARSENAME(REPLACE([horatot], ':', '.'), 3) as int)) as minutos,
                sum
                    (cast(PARSENAME(REPLACE([horatot], ':', '.'), 2) as int) * 1000 +
                     cast(PARSENAME(REPLACE([horatot], ':', '.'), 1) as int)) as milisegundos
                -- ... 
            FROM tabela
            group by
                [nome]
        ),
    
        CTE_Media as
        (
            select
                Nome,
                (minutosi - minutosp) / qtd as minutos,
                (milisegundosi - milisegundosp) / qtd as milisegundos
                -- ...            
            from CTE_Soma
        ),
    
        CTE_DateTime as
        (
            SELECT 
                nome
                dateadd(minute, minutos, dateadd(millisecond, milisegundos, 0)) as tempot
            from CTE_Media
        )
    
    select
        nome,
        convert(varchar(10), DATEDIFF(DAY, 0, tempot) * 24 + DATEPART(HOUR, tempot)) +
        RIGHT(convert(char(12), tempot, 114), 10)
    FROM CTE_DateTime

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br


    • Editado gapimex quinta-feira, 7 de janeiro de 2021 19:06
    • Marcado como Resposta claudiof quinta-feira, 7 de janeiro de 2021 20:14
    quinta-feira, 7 de janeiro de 2021 19:05