0

I feel like I'm missing something very obvious here, but I have a query where I need the result to be to 16 bits of precision after the decimal.

Below is an example query. 

select (CONVERT(decimal(38,16), 467897.78) * 12) / CONVERT(decimal(38,16), 8143372.15)

If I run that query from SSMS, the result is 0.689489 , however, if I run that exact same math problem from just the windows calculator app, the result it shows is 0.6894899627054337 . Am I missing something with my tsql query?

mituw16
  • 5,126
  • 3
  • 23
  • 48
  • Possible duplicate of [T-SQL Decimal Division Accuracy](https://stackoverflow.com/questions/423925/t-sql-decimal-division-accuracy). Note that if you leave out your `CONVERT`s altogether, you'll get `0.689489962705`, which is closer to the desired result. – Jeroen Mostert Jun 06 '19 at 13:50
  • Thanks @JeroenMostert that was it. – mituw16 Jun 06 '19 at 13:54
  • Note that, though the answers mention using `FLOAT` (and it is often the case that floating-point is either good enough, or actually what you want), `select CONVERT(DECIMAL(38, 16), 467897.78 * CONVERT(DECIMAL(18, 16), 12) / 8143372.15)` would also do in this case. It's a bit of a black art to tweak precisions for decimals, given the complicated rules, but for `FLOAT`, you're still left with formatting afterwards if you need to actually show the digits (`select FORMAT(467897.78e * 12e / 8143372.15e, 'G16')`, the default formatting has one digit less). – Jeroen Mostert Jun 06 '19 at 14:00
  • select CAST((467897.78 * 12) / 8143372.15 as Decimal(38,16)) – DrDoomPDX Jun 06 '19 at 14:35
  • @DrDoomPDX: that only casts the end result and thus has much worse accuracy; `0.6894899627050000` is accurate only to 12 digits. – Jeroen Mostert Jun 06 '19 at 15:10
  • DECLARE @a float = 467897.78 DECLARE @b float = 12 DECLARE @c float = 8143372.15 select CAST((@a * @b) / @c as decimal(38,16)) – DrDoomPDX Jun 06 '19 at 16:27
  • Try this: `SELECT CONVERT(DECIMAL(38,16),(CONVERT(decimal(10,2), 467897.78) * 12) / CONVERT(decimal(14,4), 8143372.15))` (you have a typo in your expected answer, it's ...054377, not ...054337). – Razvan Socol Jun 06 '19 at 19:01

0 Answers0