Erro para converter varchar to time

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

Msg 8115, Level 16, State 2, Line 6
Arithmetic overflow error converting expression to data type int.

Answer 6

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

Answer 7

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

Answer 8

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]

Answer 9

não resolveu o problemas com as duas funções

Answer 10

Não entendi, acho que o resultado da coluna conferencia está correto nos 2 exemplos.

---

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

Answer 11

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

Answer 12

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

Answer 13

certo mas como vou calcular para chegar na coluna conferencia.

Answer 14

O código do seu outro tópico pode ser utilizado nesse caso também:

https://social.msdn.microsoft.com/Forums/pt-BR/ba84b1ef-f5a7-469d-b39a-dc189e08f65d/converso-de-data?forum=transactsqlpt#ba84b1ef-f5a7-469d-b39a-dc189e08f65d

Espero que ajude

---

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

Answer 15

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

 

Answer 16

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

Answer 17

resolvi agora preciso somar 

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

Answer 18

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

Answer 19

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]

Answer 20

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

Answer 21

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

Answer 22

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)

Answer 23

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