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