sql-server - 将 nvarchar 转换为 int bigint 时转换失败

Question

希望这是非常简单的。我正在尝试将列转换为 int，以便我可以对其求和。

SELECT (cast(RTRIM(pricing_prices)as int)) FROM returnxx where RMAid= '5'

错误

Conversion failed when converting the nvarchar value '9.8000000000000007' to data type int.

如果我尝试 bigint，我会收到另一个错误

Error converting data type nvarchar to bigint.

Answer 1

首先转换为Numeric，然后转换为INT。试试这个:

DECLARE @Var varchar(100)='9.8000000000000007' 
SELECT CAST(CAST(@Var AS NUMERIC(35,16)) AS INT)

结果:

9

如果您想要ROUND值，只需转换为数字:

SELECT CAST(@Var AS NUMERIC)

结果:

10

如果您想要DECIMAL值，只需转换为数字:

SELECT CAST(@Var AS NUMERIC(8,2))

结果:

9.80