Beware of implicit conversion

You need to know that SQL Server does implicit conversion based on the precedence of the data types involved in the arithmetic operations.

Let us consider the following example

declare @num1 int, @num2 int
select @num1=3,@num2=2
select @num1/@num2

As you see the result is 1 and not 1.5 as expected because the data type of both the operands is INTEGER which results to 1 by truncation. The proper way to avoid this is to convert one of the operand to float or decimal

declare @num1 int, @num2 int
select @num1=3,@num2=2
select cast(@num1 as decimal(16,2))/@num2

The result is 1.5000000000000

Another method is just to multiply one of the operands by 1.0

declare @num1 int, @num2 int
select @num1=3,@num2=2
select 1.0*@num1/@num2

So be aware of this and whenever you use division make sure to convert one of the operand to decimal

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s