CODEWITHSUNDEEP
sql-servert-sql
venkatesh codetree
venkatesh codetree

Reputation: 51

Adding two time (HH:MM) columns

I have two columns containing time in format of HH:MM. for example column1 having value 149:10 and column2 having 110:20.

I want to add the both columns and I want output result in format of HH:MM

Upvotes: 2

Views: 133

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81930

Just another option

Declare @YourTable table (column1 varchar(25),column2 varchar(25))
Insert Into @YourTable values
('149:10','110:20')

Select A.*
      ,TotalTime = Format(B.Sec/3600    ,'0' )  
                  +Format(B.Sec%3600/60,':00')  
 From  @YourTable A
 Cross Apply (values (
                      (3600*parsename(replace(column1,':','.'),2)+60*parsename(replace(column1,':','.'),1))
                     +(3600*parsename(replace(column2,':','.'),2)+60*parsename(replace(column2,':','.'),1))
                     )
             ) B (sec)

Returns

column1 column2 TotalTime
149:10  110:20  259:30

Upvotes: 0

Arulkumar
Arulkumar

Reputation: 13237

Since the given values are not able to store into the TIME datatype, it will throw error as below:

DECLARE @TimeStamp1 AS TIME = '149:10'; SELECT @TimeStamp1;

So your inputs might be varchar.

I tried an approach with the given same data:

DECLARE @TimeStamp1 AS VARCHAR(20) = '149:40'; 
DECLARE @TimeStamp2 AS VARCHAR(20) = '110:40';

SELECT  CAST(
            CAST(PARSENAME(REPLACE(@TimeStamp1, ':', '.'), 2) AS INT) + 
            CAST(PARSENAME(REPLACE(@TimeStamp2, ':', '.'), 2) AS INT) + 
            ((CAST(PARSENAME(REPLACE(@TimeStamp1, ':', '.'), 1) AS INT) + CAST(PARSENAME(REPLACE(@TimeStamp2, ':', '.'), 1) AS INT)) / 60) AS VARCHAR (20)) 
        + ':' + 
        CAST((
            (CAST(PARSENAME(REPLACE(@TimeStamp1, ':', '.'), 1) AS INT) + 
            CAST(PARSENAME(REPLACE(@TimeStamp2, ':', '.'), 1) AS INT)) % 60) AS VARCHAR (20)) AS Result;

 Result
 --------
 260:20

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17126

the following query should work

declare @s1 varchar(max), @s2 varchar(max)
select @s1='149:50',@s2='110:50'

SELECT 
CAST(
    CAST(LEFT(@s1,CHARINDEX(':',@s1)-1) AS INT) + 
    CAST(LEFT(@s2,CHARINDEX(':',@s2)-1) AS INT) + 
    (CAST(RIGHT(@s1,LEN(@s1)-CHARINDEX(':',@s1)) AS INT) + CAST(RIGHT(@s2,LEN(@s2)-CHARINDEX(':',@s2)) AS INT))/60  
    as nvarchar) 
+':'+ 
CAST(
    (CAST(RIGHT(@s1,LEN(@s1)-CHARINDEX(':',@s1)) AS INT) + 
    CAST(RIGHT(@s2,LEN(@s2)-CHARINDEX(':',@s2)) AS INT))
    %60 
    AS nvarchar)

Upvotes: 0

Related Questions