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
Last activity: Mar 5, 2022, 09:02 AM