BIGINT division results in zero value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

BIGINT division results in zero value

Author

Topic

dwatrous
Starting Member

2 Posts

Posted - 2009-06-05 : 18:54:29

Hello,

If you run the following code in SQL server, the first division operation will produce zero and the second one will produce 72.6000000. Why is this?

<begin code>
DECLARE @storageTotalInBytes BIGINT,
@storageUsedInBytes BIGINT,
@storageAvailableInBytes BIGINT
select @storageUsedInBytes = 2568019160416
select @storageTotalInBytes = 3536867373728

select ROUND(@storageUsedInBytes/@storageTotalInBytes*100,1)
select ROUND(2568019160416/3536867373728*100,1)
<end code>

Please help me figure out how to make the top division operation work, since it is in a stored procedure and needs to update a database field based on the value calculated.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2009-06-05 : 21:26:32

The key to understanding this is in the documentation for the ROUND function and the division operator.

From the ROUND docs - "Returns the same type as numeric_expression."
From the division operator docs - "If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated."

So if you use two integer variables in your division you get an integer, with the fractional part truncated and then the ROUND function returns an integer too.

What you need to do is use something other than an integer for at least one of the numbers, so for example, this will give you the correct result.

DECLARE @storageTotalInBytes float,
@storageUsedInBytes float,
@storageAvailableInBytes BIGINT
select @storageUsedInBytes = 2568019160416
select @storageTotalInBytes = 3536867373728

select ROUND(@storageUsedInBytes/@storageTotalInBytes*100,1)
select ROUND(2568019160416/3536867373728*100,1)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-05 : 21:37:27

or leave the vars as BIGINT and multiply by 100.0

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 03:49:05

also see this

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/01/16/beware-of-implicit-conversions.aspx

dwatrous
Starting Member

2 Posts

Posted - 2009-06-08 : 10:47:58

Thank you so much for the responses. Here is what I ended up with:

DECLARE @storageTotalInBytes BIGINT,
@storageUsedInBytes BIGINT,
@storageAvailableInBytes BIGINT
select @storageUsedInBytes = 2568019160416
select @storageTotalInBytes = 3536867373728

select ROUND(CONVERT(float,@storageUsedInBytes)/CONVERT(float,@storageTotalInBytes)*100,1)
select ROUND(2568019160416/3536867373728*100,1)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-08 : 11:12:15

don't convert to float. Float is approximate value


select ROUND( @storageUsedInBytes * 100.0 / @storageTotalInBytes, 1)

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources