Conversion failed when converting the nvarchar to int bigint

4
votes

Hopefully this is quite straight forward. I am trying to convert a column to int so I can sum on it.

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

errors

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

If I try bigint I get another error

Error converting data type nvarchar to bigint.

2
sql-server
Try using FLOOR instead. - Larnu
SELECT TRY_CONVERT(INT, pricing_prices) FROM returnxx where RMAid= '5' returns 'TRY_CONVERT' is not a recognized built-in function name - Emma
INT and BIGINT are whole numbers, you should look at NUMERIC or DECIMAL if you want to keep decimal places - Mark Sinkinson

2 Answers

3
votes

First convert into Numeric and then INT. Try this:

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

Result:

9

If you want ROUND value, just convert into Numeric:

SELECT CAST(@Var AS NUMERIC)

Result:

10

If you want DECIMAL value, just convert into Numeric:

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

Result:

9.80
1
votes

I have this problem in the past. i was able to resolve it by casting the column to decimal first then cast it to an Integer. You can cast it to any decimal of your choice

DECLARE @pricing_prices NVARCHAR(20)='9.8000000000000007' AS pricing_prices

SELECT  CAST(CAST(@pricing_prices AS DECIMAL(9,2)) AS INT)

Output

pricing_prices
9