T-SQL Decimal Division Accuracy

by

For multiplication we simply add the number of decimal places in each argument together (using pen and paper) to work out output dec places.

But division just blows your head apart. I’m off to lie down now.

In SQL terms though, it’s exactly as expected.

--Precision = p1 - s1 + s2 + max(6, s1 + p2 + 1)
--Scale = max(6, s1 + p2 + 1)

--Scale = 15 + 38 + 1 = 54
--Precision = 30 - 15 + 9 + 54 = 72
--Max P = 38, P & S are linked, so (72,54) -> (38,20)
--So, we have 38,20 output (but we don use 20 d.p. for this sum) = 11.74438969709659
SELECT CONVERT(DECIMAL(30,15),146804871.212533)/CONVERT(DECIMAL (38,9),12499999.9999)


--Scale = 15 + 38 + 1 = 54
--Precision = 30 - 15 + 15 + 54 = 84
--Max P = 38, P & S are linked, so (84,54) -> (38,8)
--So, we have 38,8 output = 11.74438969
SELECT CONVERT(DECIMAL(30,15),146804871.212533)/CONVERT(DECIMAL (38,15),12499999.9999)

You can do the same math if follow this rule too, if you treat each number pair as

  • 146804871.212533000000000 and 12499999.999900000
  • 146804871.212533000000000 and 12499999.999900000000000

More Related Contents:

  1. Levenshtein distance in T-SQL
  2. How to get sp_executesql result into a variable?
  3. How can I use optional parameters in a T-SQL stored procedure?
  4. Select query to remove non-numeric characters
  5. Selecting a Record With MAX Value
  6. Combining INSERT INTO and WITH/CTE
  7. T-SQL Pivot? Possibility of creating table columns from row values
  8. What is a “batch”, and why is GO used?
  9. TSQL divide by zero encountered despite no columns containing 0
  10. T-SQL loop over query results
  11. How to insert default values in SQL table?
  12. How to Round a Time in T-SQL
  13. How to convert a “dd/mm/yyyy” string to datetime in SQL Server?
  14. Return a value if no rows are found in Microsoft tSQL
  15. Check if a varchar is a number (TSQL)
  16. How to pivot table with T-SQL?
  17. Using Aliases in Where Clause or an Alternative Option?
  18. Extract the first word of a string in a SQL Server query
  19. Testing for inequality in T-SQL
  20. Can I use the MERGE statement in SQL Server 2005?
  21. Replace a newline in TSQL
  22. Search for one value in any column of any table inside a database
  23. How to use an Alias in a Calculation for Another Field
  24. Check if a varchar is a number (T-SQL)
  25. Using a cursor with dynamic SQL in a stored procedure
  26. Truncate Datetime to Second (Remove Milliseconds) in T-SQL
  27. Poor Man’s SQL Pivot. List Questions as Columns and Answers per User in one row
  28. String.Format like functionality in T-SQL?
  29. How to parse string into date?
  30. Stored procedure hangs seemingly without explanation

Leave a Comment