CODEWITHSUNDEEP
t-sqldatetimetimespan
Davood
Davood

Reputation: 5645

How to sum time(7) data types in T-SQL?

How to sum two times data type in t-sql?

 declare @fTime time(7)='05:22';
 declare @tTime time(7)='06:00';
 declare @result time(7);

I used this but it doesn't work.

set  @result=sum(@fTime+@tTime);

Upvotes: 0

Views: 231

Answers (2)

Menno
Menno

Reputation: 12661

You could add the minutes of one to the other like so:

select dateadd(minute,datediff(minute,'00:00',@tTime),@fTime)

However, note that the comment by dlatikay is correct, time(7) is not meant for durations. It would be better to store durations as an integer, for example by storing minutes or seconds.

Upvotes: 1

SqlKindaGuy
SqlKindaGuy

Reputation: 3591

Can you use something like this? 

DECLARE @fTime TIME(7) = '05:22';
DECLARE @tTime TIME(7) = '06:00';
DECLARE @result TIME(7);

SELECT @result = CAST([Hours] AS NVARCHAR) + ':' + CAST([Minutes] AS 
NVARCHAR)
FROM (
SELECT cast(TotalTime AS INT) / 60 AS [Hours]
    ,CAST(TotalTime AS INT) % 60 AS [Minutes]
FROM (
    SELECT fTime + tTime AS TotalTime
    FROM (
        SELECT cast(SUBSTRING(cast(@ftime AS NVARCHAR), 1, 2) AS INT) * 60 + 
CAST(substring(cast(@ftime AS NVARCHAR), 4, 2) AS INT) AS fTime
            ,cast(SUBSTRING(cast(@tTime AS NVARCHAR), 1, 2) AS INT) * 60 + 
CAST(substring(cast(@tTime AS NVARCHAR), 4, 2) AS INT) AS tTime
        ) x
    ) y
) z

SELECT @result

Upvotes: 0

Related Questions