CODEWITHSUNDEEP
sqlsql-server
alias
alias

Reputation: 1

I have column x in hh:mm format of datatype varchar in SQL Server and I want to perform sum on that 'x' column

I have column x in hh:mm format of datatype varchar in SQL Server and I want to perform sum on that x column.

I created a user-defined function to convert total min into hh:mm format.

Then I tried to perform sum to calculate total duration:

sum(cast(new_totalmin AS Int))
  1. also i want total of HH:mm exactly as example 4:20 +1:10

5:30 5 hour: 30 minute

  1. or i can do one thing here insted hh:mm i keep column as it is which is totalmin as int once sum cal insted of hh:mm (hh.mm which is in decimal also ok for me PSB it will be ok for me ':' or '.' format ) (60 min --> 1:00 --> 1.00

          90 min -->  1:30   -->1.30
     ---------------------------------

    sum --> 150 min -->2:30 --> 2.30)

but it did not work.

I got an error like

Conversion failed when converting the varchar value '01:00' to data type int

Upvotes: 0

Views: 283

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

I would recommend that you store numeric values -- such as the number of minutes -- as a number rather than a string.

The challenge is converting the value back to an HH:MM format. SQL Server does not support time values of 24 hours or greater, so you need to use string manipulations.

Assuming that your values are all less than 24 hours, you can use:

select sum(datediff(minute, 0, hhmm)) as num_minutes,
       concat(sum(datediff(minute, 0, hhmm)) / 60, ':',
              format(sum(datediff(minute, 0, hhmm)) % 60, '00')
             )
from t;

The result here is a string, so this can exceed 24 hours.

A more general solution eschews date/times altogether:

select sum(v.minutes) as num_minutes,
       concat(sum(v.minutes) / 60, ':',
              format(sum(v.minutes) % 60, '00')
             )
from t cross apply
     (values (left(t.hhmm, charindex(':', t.hhmm) - 1) * 60 + right(t.hhmm, 2))
     ) v(minutes);

Here is a db<>fiddle.

Upvotes: 0

Raihan
Raihan

Reputation: 407

DECLARE @SampleData AS TABLE (HourMinutes VARCHAR(10));

INSERT INTO @SampleData VALUES ('4:32');
INSERT INTO @SampleData VALUES ('5:28');
INSERT INTO @SampleData VALUES ('6:00');
INSERT INTO @SampleData VALUES ('7:10');

SELECT * FROM @SampleData

SELECT SUM(datediff(minute, 0, HourMinutes)) TotalMinute 
FROM @SampleData

You will get following output

enter image description here

Upvotes: 1

Venkataraman R
Venkataraman R

Reputation: 13009

hh:mm is a varchar data and applying SUM will not work on it.

As you are telling that you are already having a function, I would suggest you to perform sum of the minutes and then later convert them to hh:mm

SELECT ... , YourUserDefinedFunction(sum(minuteData)) as minutesInHHMM_Format
FROM ...
WHERE ...
GROUP BY ...

Upvotes: 0

Related Questions