Blog Post

Rounding versus truncating

,

Be careful when implicitly converting data types in T-SQL.  Directly assigning 1.5 (either as a FLOAT or a NUMERIC) to an INT value in SQL Server may truncate the value to 1 rather than rounding it up as you might expect.  Explicitly calling ROUND( ,0) as part of the assignment will round 1.5 up to 2.

Here's an example:

DECLARE @Value INT;

DECLARE
@NumericValue NUMERIC(10,1) = 1.5;

DECLARE
@FloatValue FLOAT(53) = 1.5;


SET @Value = @NumericValue;  --Not calling ROUND()

SELECT
@Value; --Returns 1


SET @Value = @FloatValue; --Not calling ROUND()

SELECT
@Value; --Returns 1


SET @Value = ROUND(@NumericValue,0);

SELECT
@Value; --Returns 2


SET @Value = ROUND(@FloatValue,0);

SELECT
@Value; --Returns 2

Rate

Share

Share

Rate