Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

New York City .NET

The author of the NYCdotNet blog is a VB.NET and SQL server developer living and working in New York City. The author seeks to promote clean, readable, efficient code in both VB and SQL from design to development, deployment, and application maintenance.

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



Comments

Leave a comment on the original post [nycdotnet.blogspot.com, opens in a new window]

Loading comments...