CODEWITHSUNDEEP
sqlsql-serverdatetimesql-server-2012
Anyname Donotcare
Anyname Donotcare

Reputation: 11423

How to sum set of period times in specific format

If I have result set like that :

Work_hour(hh:mm)
10:24
12:59
06:28

where Work_hour is of type varchar

How to sum those hours and minutes with the same format ?

Upvotes: 0

Views: 93

Answers (2)

Devart
Devart

Reputation: 122032

Try this one -

Query:

DECLARE @temp TABLE
(
      work_hour CHAR(5)
)

INSERT INTO @temp (work_hour)
VALUES 
    ('10:24'),
    ('12:59'),
    ('06:28')

;WITH cte AS
(
    SELECT mn = SUM(DATEDIFF(MINUTE, '19000101', CAST('19000101 ' + work_hour AS DATETIME)))
    FROM @temp
)
SELECT CAST(FLOOR(mn / 60) AS VARCHAR(5)) + ':' + CAST(mn % 60 AS VARCHAR(2))
FROM cte

Output:

hm
--------
29:51

Update 2:

DECLARE @temp TABLE
(
      transtime_out DATETIME
    , transtime_in DATETIME
)

INSERT INTO @temp (transtime_out, transtime_in)
VALUES 
    ('2013-05-19 16:40:53.000', '2013-05-19 08:58:07.000'), 
    ('2013-05-19 16:40:53.000', '2013-05-19 08:58:07.000')

SELECT diff = LEFT(CONVERT(VARCHAR(10), CAST(SUM(CAST(a.transtime_out - a.transtime_in AS FLOAT)) AS DATETIME), 108), 5)
FROM @temp a

Upvotes: 2

heikkim
heikkim

Reputation: 2975

SELECT CAST(FLOOR(TMP1.MINS/60) AS VARCHAR) + ':' + CAST((TMP1.MINS % 60) AS VARCHAR) FROM (
    SELECT SUM (CAST(LEFT(time_column, 2) AS INT) * 60 + CAST(RIGHT(time_column, 2) AS INT)) as MINS FROM table1
) AS TMP1

Where time_column is the column in the table and table1 is the name of the table. Example:

create table table1 (
    time_column varchar(10)
);
insert into table1 (time_column) values ('12:20'), ('10:40'), ('15:50');

Results in: 38:50

Upvotes: 3

Related Questions