我需要添加两列,将时间值存储为小数示例:1.(8.30+0.32=9.02(输出应为9.03而非8.62(
上面提到的示例运行良好,是下面SQL的实际输出。
我写了下面的SQL,它主要工作得很好,但我发现很少有添加不正确的的情况
示例: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"
在上述案例中,我找不出哪里出了问题。这里有什么问题吗?或者有什么更好的方法来处理这个添加的吗
试试这个:
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)
);
首先,转换为TIME
,得到总分钟数。然后,将分钟格式化为Time
,但使用.
而不是:
。
代码
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
输出
7.737