Error trying to add time field (varchar) sql server

Question

Error trying to add time field (varchar) sql server

Navigation

#1 by (3 votes)

2

I have a query that returns the following data:

SELECT * FROM METAS

   mes  lj  saldo   meta
    4   2   153:59  123:00:00                                         
    4   2   25:33   00:00:00
    4   2   29:57   08:00                                             
    4   2   38:58   45:00:00                                          
    4   2   94:47   20:00                                             
    4   2   23:41   15:00:00                                          
    4   2   215:37  70:00:00                                          
    4   2   213:41  120:00:00                                         
    4   2   235:38  80:00:00                                          
    4   2   36:13   13:00:00

The more accurate it is done this way:

SELECT 
  mes,
  lj,
  SUM(saldo) as salto_total,
  SUM(meta) as meta_total
   FROM METAS

It returns me the following error:

Message 8117, Level 16, State 1, Line 1 Operand data type varchar is invalid for sum operator.

The reason is that my balance and meta fields are of the varchar format.

I'll be introduced to this hint

Plus it returns the following error:

Message 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '00: 00' to data type int.

How could you do this calculation?

sql sql-server

asked by anonymous 08.05.2018 / 01:04

1 answer

Is it possible to insert parameters in the filters attributes? Change CSS with Jquery

score 3 · Accepted Answer

This is an initial suggestion, considering the information provided so far:

-- código #1 v3
declare @Mes int;
set @Mes= 4;

with 
normalizaHMS as (
SELECT lj, 
       case len(saldo) - len(replace(saldo, ':', '')) 
            when 1 then saldo + ':00'
            when 2 then saldo end as saldo,
       case len(meta) - len(replace(meta, ':', '')) 
            when 1 then meta + ':00'
            when 2 then meta end as meta
  from METAS
  where mes = @Mes
),
separaCampos as (
SELECT lj, saldo, meta,
       cast(parsename(replace(saldo, ':', '.'), 3) as int) as saldoH,
       cast(parsename(replace(saldo, ':', '.'), 2) as int) as saldoM,
       cast(parsename(replace(meta, ':', '.'), 3) as int) as metaH,
       cast(parsename(replace(meta, ':', '.'), 2) as int) as metaM
  from normalizaHMS
),
somaCampos as (
SELECT lj, 
       sum(saldoH) as sum_saldoH,
       sum(saldoM) as sum_saldoM,
       sum(metaH) as sum_metaH,
       sum(metaM) as sum_metaM
  from separaCampos
  group by lj
),
agregaCampos as (
SELECT lj, 
       (sum_saldoH + (sum_saldoM / 60)) as sum_saldoH,
       (sum_saldoM % 60) as sum_saldoM,
       (sum_metaH + (sum_metaM / 60)) as sum_metaH,
       (sum_metaM % 60) as sum_metaM
  from somaCampos
)
SELECT @Mes as mes, lj, 
       cast(sum_saldoH as varchar) + ':' + right('00' + cast(sum_saldoM as varchar), 2) as saldo,
       cast(sum_metaH as varchar) + ':' + right('00' + cast(sum_metaM as varchar), 2) as meta
  from agregaCampos;