Safely convert REAL to INT when values exceed INT range?

Question

I'm working with a SQL Server database and I have a column Quantity in my Product table which is of REAL data type. I want to convert this column to INT, but some of the REAL values are greater than the maximum INT value (2147483647).

When I try to update the Quantity column with the following query:

UPDATE p
SET p.Quantity = CASE 
    WHEN p.Quantity > CAST(2147483647 AS REAL) THEN 2147483647
    ELSE CAST(p.Quantity AS INT)
END
FROM Product p WITH(ROWLOCK)

I get an arithmetic overflow error:

Arithmetic overflow error for type int, value = 2147483648.000000

Moreover, when I set the Quantity as 2147483647 cast as REAL:

UPDATE Product
SET Quantity = CAST(2147483647 AS REAL) 
WHERE id = '123'

The Quantity becomes 2.147484E+09 (which is 2147483648 when converted use STR(quantity)), and the previous update query still fails with the same error.

How can I safely convert these REAL values to INT in SQL Server when some values exceed the INT range?

And if possible please explain me about the this

If I set quantity to a number 2147483xxx which 3 last number is in range [584 -> 777) it will become 2147483658

520: [457 -> 584)

658: [584 -> 777)

Answer 1

Just use TRY_CAST

UPDATE p
SET p.Quantity = 2147483647
FROM Product p
WHERE TRY_CAST(p.Quantity AS INT) IS NULL;