Rounding Issue - SQL Server Forums

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)

Rounding Issue

Author

Topic

estaup
Starting Member

3 Posts

Posted - 2011-11-11 : 15:57:53

Why are the results from these two so different? And how can I control the result being returned more?

Thanks.

select ((126747.09-(19027.00+83192.592500+0.000000+0.000000+0.000000+0.000000))/321122.59)*100

select ((Cast('126747.09' as numeric(28,10))-(cast(19027.00 as numeric(28,10))+Cast(83192.592500 as numeric(28,10))+Cast(0 as numeric(28,10))+Cast(0 as numeric(28,10))+Cast(0.000000 as numeric(28,10))+Cast(0 as numeric(28,10))))/Cast(321122.59 as numeric(28,10)))*100

Results:
7.638047980367800

7.638000

estaup
Starting Member

3 Posts

Posted - 2011-11-11 : 16:55:47

I have determined that the actual rounding is taking place at the time of division.

estaup
Starting Member

3 Posts

Posted - 2011-11-11 : 17:04:58

I found the problem. Maximum precision in SQL Server 2005 is 38. When I am dividing two 28,10 decimals by each other, the precision exceeds the maximum of 38 so SQL Server performs rounding in order to retain values. By changing my casting to be numeric(20,10) was able to produce the correct result with much better precision.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources