Sample Header Ad - 728x90

Automatic decimal rounding issue

13 votes
3 answers
67220 views
The question is relatively simple. I need to calculate 3 columns where the mid results are huge decimals, and I'm running into a problem early on with SQL Server basically rounding the decimals regardless of any cast / converts. For example, let's do a simple division as 1234/1233. A calculator will produce 1,00081103000811. But when I do this on SQL Server, we get the following: -- Result: rounded at 1.000811000... with trailing zeroes up until the 37 precision SELECT CAST(CAST(1234 AS DEC(38,34))/CAST(1233 AS DEC(38,34)) AS DEC(38,37)) -- Result: rounded at 1.000811 SELECT CONVERT(DECIMAL(38,32), 1234)/CONVERT(DECIMAL(38,32),1233) -- Correct result at 1,00081103000811 -- But this requires the zeroes to be put in manually when you don't -- even know the precision of the end result SELECT 1234.0/1233.00000000000000 Why does this automatic rounding occur? And what's the best way to calculate insanely long decimal values when you can't be sure how big a number (the int or dec part) will be, since the table can contain various different values? Thanks!
Asked by Kahn (1803 rep)
May 8, 2013, 08:15 AM
Last activity: Mar 5, 2022, 09:02 AM