locked
Hi how can we add colounm having varchar data in form hh:mm:ss RRS feed

  • Question

  • There is a column named Timings in HH:MM:SS format.
    Datatype of this column is varchar(50).

    I want to sum the rows in this column and get the output as one single record.

    00:01:06
    00:01:16
    00:01:04
    00:01:24
    00:01:13
    00:01:06
    00:02:21
    00:01:16

    Please help me with a query for this.

    Thannk you.
    Monday, May 25, 2015 8:46 AM

Answers

  • are you looking for this?

    The idea is to convert the time into seconds (assuming all time falls on the same day) and sum them up. The final result is in seconds which can be converting into minutes, hours etc

    CREATE TABLE Table1
        (tm varchar(8))
    ;
        
    INSERT INTO Table1
        (tm)
    VALUES
        ('00:01:06'),
        ('00:01:16'),
        ('00:01:04'),
        ('00:01:24'),
        ('00:01:13'),
        ('00:01:06'),
        ('00:02:21'),
        ('00:01:16')
    ;
    select sum( datediff(s,convert(time,(dateadd(s,0,0))), convert(time,tm))) from table1


    Satheesh
    My Blog | How to ask questions in technical forum

    • Proposed as answer by Eric__Zhang Tuesday, May 26, 2015 5:56 AM
    • Marked as answer by pituachMVP Thursday, June 18, 2015 6:32 PM
    Monday, May 25, 2015 9:17 AM
  • Try this code to get the SUM here

    DECLARE @TimeTable TABLE ( TimeFormat varchar(50))
    
    INSERT INTO @TimeTable VALUES
    ('00:01:06'),
    ('00:01:16'),
    ('00:23:04')
    
    
    DECLARE @TotalTime INT= (SELECT SUM(( DATEPART(hh, TimeFormat) * 3600 ) +
                              ( DATEPART(mi, TimeFormat) * 60 ) +
                                DATEPART(ss, TimeFormat)) 
    FROM   @TimeTable)
     
    SELECT TOP 1  @TotalTime / ( 24 * 3600 ) as total_days, convert(time,DATEADD(s, @TotalTime, 0)) AS TimeSpent
    FROM   @TimeTable

    Output: 00:25:26.0000000

    Hope this will help


    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.


    My Profile on Microsoft ASP.NET

    • Proposed as answer by Eric__Zhang Tuesday, May 26, 2015 7:28 AM
    • Marked as answer by pituachMVP Thursday, June 18, 2015 6:32 PM
    Monday, May 25, 2015 9:18 AM

All replies

  • SELECT CAST(DATEADD(ms, SUM(DATEDIFF(ms, '00:00:00.000', timecol) ), '00:00:00.000') AS TIME(0)) as TI 
    FROM  (VALUES ('00:01:06'),('00:01:16'))  AS der (timecol)

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, May 25, 2015 9:13 AM
    Answerer
  • are you looking for this?

    The idea is to convert the time into seconds (assuming all time falls on the same day) and sum them up. The final result is in seconds which can be converting into minutes, hours etc

    CREATE TABLE Table1
        (tm varchar(8))
    ;
        
    INSERT INTO Table1
        (tm)
    VALUES
        ('00:01:06'),
        ('00:01:16'),
        ('00:01:04'),
        ('00:01:24'),
        ('00:01:13'),
        ('00:01:06'),
        ('00:02:21'),
        ('00:01:16')
    ;
    select sum( datediff(s,convert(time,(dateadd(s,0,0))), convert(time,tm))) from table1


    Satheesh
    My Blog | How to ask questions in technical forum

    • Proposed as answer by Eric__Zhang Tuesday, May 26, 2015 5:56 AM
    • Marked as answer by pituachMVP Thursday, June 18, 2015 6:32 PM
    Monday, May 25, 2015 9:17 AM
  • Try this code to get the SUM here

    DECLARE @TimeTable TABLE ( TimeFormat varchar(50))
    
    INSERT INTO @TimeTable VALUES
    ('00:01:06'),
    ('00:01:16'),
    ('00:23:04')
    
    
    DECLARE @TotalTime INT= (SELECT SUM(( DATEPART(hh, TimeFormat) * 3600 ) +
                              ( DATEPART(mi, TimeFormat) * 60 ) +
                                DATEPART(ss, TimeFormat)) 
    FROM   @TimeTable)
     
    SELECT TOP 1  @TotalTime / ( 24 * 3600 ) as total_days, convert(time,DATEADD(s, @TotalTime, 0)) AS TimeSpent
    FROM   @TimeTable

    Output: 00:25:26.0000000

    Hope this will help


    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.


    My Profile on Microsoft ASP.NET

    • Proposed as answer by Eric__Zhang Tuesday, May 26, 2015 7:28 AM
    • Marked as answer by pituachMVP Thursday, June 18, 2015 6:32 PM
    Monday, May 25, 2015 9:18 AM