Question: Why does SELECT 1/2 return 0? Both operands are integers. SQL Server performs integer division and truncates the fractional part of the result.
At a user-group meeting, this small expression surprised a beginner. I have seen the same reaction many times: “Is it a bug?” The useful lesson is to inspect the types of the expression, rather than judging it by how we write arithmetic on paper.
SELECT 1/2 AS IntegerHalf;
SELECT 1/2.0 AS DecimalHalf;
SELECT SQL_VARIANT_PROPERTY(2.0,'BaseType') AS DenominatorType;
SELECT CAST(1/2 AS decimal(10,2)) AS CastTooLate,
CAST(1 AS decimal(10,2))/2 AS CastBeforeDivision;
SELECT -1/2 AS NegativeIntegerHalf;
Change an operand before doing the division
SELECT 1/2.0 returns 0.500000. The decimal-point constant 2.0 is a decimal/numeric value, not FLOAT as the original explanation said. The type rules change the division itself.
SQL Server does not first keep 0.5 and merely display it as an integer. Integer division already produced 0. That is why casting 1/2 afterwards produces 0.00: the fraction has been lost. Casting an operand to DECIMAL before division preserves the fractional calculation.
Truncation is toward zero, so -1/2 is also 0. Choose a decimal precision and scale that fit the real numerator and denominator, not just this small demonstration. A ratio stored into an integer column can lose the fraction later too, even if its expression was decimal.
The earlier data-type precedence article explains the broader rule.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
21 Comments. Leave new
Hi Pinal
Thanks for sharing your knowledge. I want to know that when we try to convert whole calculation explicitly into float then why it does not work?
SELECT CAST((1/2) AS FLOAT) — This is not working. It will give result as 0.
If I try will to convert it like below manner i.e. individually then it will give correct result as 0.5.
SELECT CAST(1 as FLOAT)/CAST(2 as FLOAT)
SELECT CAST(1 AS FLOAT)/2
SELECT 1/CAST(2 AS FLOAT)
Any help would be grateful.
Warm Regards
Anuj Soni
Dear Anuj Soni,
I think it’s about the execution or evaluation order of the expression.
At first the tsql interpreter evaluates the calculation inside: 1/2 = 0.
And just after it casts this result.
Before converting compiler parses 1/2 then converts to FLOAT hence it still returns 0
Anuj, You are converting to float after the result is produced (1/2 is 0) hence conversion of float is 0. You need to convert one of the values into float before division
SELECT cast(1 as float)/2
Now the result is 0.5
Hi Anuj,
This is what you are expecting?
select convert(float,1/2.0 )
Why doesn’t SQL round up if the resultset is an integer? 0.5 would round up to 1 in most cases except this one.
SELECT 1.0/2 works too.
@Anuj, your cast wont work because the implicit conversion has already happened before. You can see the latter three statements enforce the cast before division.
SELECT 1./2
SELECT 1/2.
@John – Good question. It is because SQL isn’t rounding, it is using the integer part of the answer. Try SELECT 2/3 or SELECT 99/100
Awesome TIP
Pinal, please keep educating the community and appreciate your volunteership.
Whoever sees this TIP will definitely say lesson learned for the day
I await some such knowledge
Note nobody can steel individuals knowledge
Here is similar post on Beware of Implicit conversion
the same:
select cast(0.5 as int)
Thanks Guys for your valuable feedback.
Yep. I understood the process why it was not coming 0.5 and instead shows 0 because it does division before conversion.
when you write the statement SELECT 1/2, SQL Server recognizes the numbers 1 and 2 as integers. Since the value 1/2 is .5, this is not an integer. SQL Server uses a specific rounding method, which results in Zero. If you instead wrote the command as SELECT 1. / 2. you would get the more accurate result of .5. This is because 1. and 2. are implicitly assigned a float data type, if memory serves me right. Regardless, they are not integers, and decimal math is then available.
Feel free to post my web site as www. sqlsageadvice.com if I meet your requirements.
Ben
@Ben – If you want to build up a following you need to put in an immense effort like Pinal. I see from your site that the last post was 18 months ago. The specific rounding method you mention above rounding down, or floor-ing. Also you suggest that zero is not an accurate result, but it is. Just because the expectation was to see a floating point answer, doesn’t make the integer one wrong.
Dividing 1/2 in most regular coding always gives the answer of 0 for the same reason SQL Server does so. It blew my mind when I was learning VB.NET that the answer was “1”, then I found out that you have to do 1\2 to get a truncated down answer.
Oops. Should have read 1/2 = 0.5 ;-)
Awesome question
Not everybody knows this functionality of SQL rule
Pinal please keep publishing such interesting articles.
I enjoyed reading this article and recommend others.
Thanks for educating the community and appreciate your volunteership
If you are dealing with columns is col1/(1.0*col2)
That’s not a float data type, but it’s a numeric data type. Please correct this article, thank you.