CODEWITHSUNDEEP
sql-server-2008
shanish
shanish

Reputation: 1984

Sql Sum specific times

I have a varchar type column in my sql table, and am collecting the Working hours from that... If I give workig hours like

WorkingHours
------------
     1
     5
     8

I can find the sum of this WorkingHours column value like Sum(WorkingHours), but here I need to give more specific timings like

WorkingHours
------------
    01:30
      2
    01:15
    04:45

like this, is it possible to sum these values, I tried the same way, but I couldn't come up with any solution, can anyone help me here....

Upvotes: 0

Views: 246

Answers (2)

gbn
gbn

Reputation: 432261

Why do you store times as varchar? Use time or datetime of course...

Note that "2", if convertable, would be 48 hours or 2 days. If you don't want to fix your data or datatypes, then you'll have to workaround this

DECLARE @feckedTable TABLE (WorkingHours varchar(5));

INSERT @feckedTable (WorkingHours) VALUES ('01:30'), ('02:00'), ('01:15'), ('04:45');

SELECT
    SUM(DATEDIFF(minute, 0, CAST(WorkingHours AS smalldatetime))) AS TotalTime
FROM
    @feckedTable

SELECT
    CAST(TotalTime/60 AS varchar(4)) + ':' + CAST(TotalTime % 60 AS char(2))
FROM
    (
    SELECT
        SUM(DATEDIFF(minute, 0, CAST(WorkingHours AS smalldatetime))) AS TotalTime
    FROM
        @feckedTable
    ) X

Upvotes: 2

AurA
AurA

Reputation: 12363

select cast(dateadd(millisecond,sum(datediff(millisecond,0,cast(totalhrs as datetime))),0) as time) from table

This is the syntax for getting accuracy upto milliseconds.

Refer to this solution for detailed explaination.

Upvotes: 1

Related Questions