Division operator in SQL Server returns 0 always

This is very small thing even I tempted to write about this topic because I have found this kind of questions in several forums. This is just a lack understanding. Suppose I have some value to calculate like (2/10)*100, what should be the answer??? Of course, it’s very simple, even primary school students can answer it. Answer is 20.

Now run following query in your SQL Server’s query analyzer.

select ((2/10)*100)

It will return 0 rather than 20. Why? Is there any problem or bug in SQL Server for division or multiplication operator? Answer is NO. There is no bug you are trying to divide the integer value with integer so 2/10 is 0.20 and it will be rounded up into integer and become 0.

So, here is the reason but how to solve it. There are few ways to do so.

Either covert your 2 to double or multiply 2 with some double value then SQL Server engine will take care of rest of the task.

Run following query and you will get perfect result.

–multyplying our integer value 2 with float value 1.0 to convert int 2 to decimal 2

select ((2*1.0)/10)*100

–or

–used convert function to change integer to decimal

select (convert(decimal(5,2),2)/10*100)

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

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

%d bloggers like this: