我試圖理解為什么當我將值從VARCHAR(MAX)to 轉換時 SQL Server 會修改我的比例值NUMERIC(P,S)。
這是示例：
--declare @val1 as numeric (18,6)
--declare @val2 as numeric (18,6)
DECLARE @val1 AS VARCHAR(MAX)
DECLARE @val2 AS VARCHAR(MAX)
SET @val1 = '2383.4912500000'
SET @val2 = '2383.4912000000'
DROP TABLE IF EXISTS TMP
SELECT
V1 = @VAL1,
VC1 = CAST(@VAL1 AS NUMERIC(30, 20)),
valDiff = ABS(CAST(CAST(@VAL2 AS NUMERIC(18, 6)) - CAST(@VAL1 AS NUMERIC(18, 6)) AS NUMERIC(30, 20)) / @VAL1)
INTO
TMP
SELECT * FROM TMP
-- when variable is VARCHAR(MAX), valDiff == 0.00000002 which is correct, but rounded.
-- when the variable is NUMERIC(18, 6), valDiff == 0.0000000209776310275945 which is correct and not rounded
uj5u.com熱心網友回復：
當您從精度非常高的小數開始時，算術運算的結果可能會縮小其比例以保留更多有效數字。如果值在小數點左側沒有30 個非零數字，則無關緊要。轉換基于宣告的精度和比例：
精度和規模
在這里，您varchar正在以過高的精度隱式轉換為小數。
由于需要小心地使用小數轉換中間結果，使用 執行所有計算通常更容易，float它在所有尺度上保持約 15 位有效數字的精度，并且僅將最終結果轉換decimal為存盤。例如
declare @val1 as float ='2383.4912500000'
declare @val2 as float ='2383.4912000000'
select cast( abs(@VAL2 - @VAL1) / @VAL1 as numeric(30,20) )
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/421780.html
標籤：
上一篇：創建多個存盤程序的腳本