How to add two time values stored as decimal in SQL Server

Question

I need to add two columns which stores time values as decimals example: 1.) 8.30+0.32 = 9.02 (output should be 9.03 not 8.62)

The above mentioned example is working fine and is an actual output of the below SQL.

I wrote the below SQL which works fine predominantly, but I find few cases where the addition is not proper

Example: 3.57+5.25=18.44

SELECT
case when B.Column_B is null then A.Column_A
when B.Column_B is not null then 
replace(CONVERT(varchar(5), 
DATEADD(ss,(SUM((DATEPART(hh, replace(isnull(a.Column_A,0.00),'.',':'))*3600) + (DATEPART(mi,replace(isnull(a.Column_A,0.00),'.',':'))*60)) +
SUM((DATEPART(hh, replace(isnull(b.Column_B,0.00),'.',':'))*3600) + (DATEPART(mi,replace(isnull(b.Column_B,0.00),'.',':'))*60))),0),108) ,':','.')
End as "Total_Hours"

I am not able to find where this is going wrong in the above mentioned case. Is there anything wrong here or is there any better way of handling this addition

Why can't you use the time type? – Tim Biegeleisen Mar 06 '20 at 05:59 — Tim Biegeleisen, Mar 06 '20 at 05:59

score 2 · Accepted Answer · answered Mar 06 '20 at 06:24

2

Try this:

DECLARE @Value01 DECIMAL(9,2) = 8.30
       ,@value02 DECIMAL(9,2) = 0.32;

SELECT CONCAT 
       (
            (DATEDIFF(MINUTE, '0:00:00', CAST(REPLACE(@Value01, '.', ':') AS TIME)) +  DATEDIFF(MINUTE, '0:00:00', CAST(REPLACE(@value02, '.', ':') AS TIME))) / 60
           ,'.'
           ,RIGHT((DATEDIFF(MINUTE, '0:00:00', CAST(REPLACE(@Value01, '.', ':') AS TIME)) +  DATEDIFF(MINUTE, '0:00:00', CAST(REPLACE(@value02, '.', ':') AS TIME))) % 60 + 100, 2)
       );

First, convert to TIME and get the total number of minutes. Then, format the minutes to Time but using . instead :.

answered Mar 06 '20 at 06:24

gotqn

42,737
46
157
243

Great this works fine Thanks. But, what was the issue with the conversion which I have done. Does doing the conversion other way around does not work? – Venkat Mar 10 '20 at 05:32
@Venkat I did not debug your code. There are different ways to solve an issue in T-SQL - you need to find usually the most efficient one in terms of performance. – gotqn Mar 10 '20 at 06:19

score 0 · Answer 2 · answered Mar 06 '20 at 14:04

0

Code

declare @v1 DECIMAL(9,3) = 4.369,  -- 9 characters with 3 decimal places.
@v2 DECIMAL(9,3) = 3.368  -- 9 characters with 3 decimal places.
print @v1 + @v2

Output

7.737

answered Mar 06 '20 at 14:04

Yogendra

504
5
13

How to add two time values stored as decimal in SQL Server

2 Answers2