Error trying to add time field(varchar) sql server

Asked

Viewed 477 times

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

But I need it done that 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, Status 1, Line 1 Operand data type varchar is invalid for sum Operator.

The reason is that my balance and goal fields are varchar format.

I’ll get that one tip

But it returns the following error:

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

How could I make that calculation ?

sql sql-server

  • when the goal is 08:00, the correct would be 00:08:00 né (HH:mm:ss) ?! you have a serious problem storing this data. A simple numeric field or integer would solve the problem

  • and the balance... 213:41 would be 213:41:00 since in the middle it does not have 3 decimal places (HH:mm) ?

  • @Rovannlinhalis Expected results are : goal:1068:04:00 Balance: 494:00:00

  • look at the trouble it is to normalize these data as @Josediz posted... I would think about redoing these columns soon...

  • For accumulation of hours that exceed 24h the suggestion is the use of numerical column, using minutes as a unit. In this way, any calculation is simple and converting to hhh:mm is also simple.

  • I understand and agree with you , but these data that comes from another endeavour where I can not manipulate the structure of the database, I can consult, more I can not change

Show 1 more comment

1 answer

3


This 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;

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.