9

I have a view in SQL server, something like this:

select 6.71/3.41 as NewNumber

The result is 1.967741 (note 6 decimal points) -> decimal (38,6)

I try the same thing in a calculator but the result is 1.967741935483871xxxx

I want to force SQL Server to return more accurate result something like decimal(38,16) I have tried the obvious things like casting, but SQL Server doesn't improve the output I just get some trailing zeros at the end like 1.9677410000

Is there a way to force SQL Server to not truncate the result or give more accurate one?

Mark Sowul
  • 10,244
  • 1
  • 45
  • 51
Chris
  • 93
  • 1
  • 1
  • 3

4 Answers4

17

If you want something like decimal(38,16) then you need to cast the inputs not the output after truncation has already occurred!

SELECT CAST(6.71 AS DECIMAL(38,18))/3.41 AS NewNumber

Returns

1.9677419354838709

Check the datatype

SELECT 
SQL_VARIANT_PROPERTY(CAST(6.71 AS DECIMAL(38,18))/3.41, 'BaseType'),
SQL_VARIANT_PROPERTY(CAST(6.71 AS DECIMAL(38,18))/3.41, 'Precision'),
SQL_VARIANT_PROPERTY(CAST(6.71 AS DECIMAL(38,18))/3.41, 'Scale')

Returns

numeric 38  16

Edit

This is just to add an additional link as follow up to the comments. The rules for decimal to decimal conversion are described in BOL. That link includes the following phrase

*The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

but leaves it unspecified exactly how such truncation is performed. This is documented here.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • why Cast(6.71 as decimal(38,16))/Cast(3.41 as decimal(38,16)) returns 1.967741? – amit_g Mar 22 '11 at 00:44
  • 2
    The result is datatype `decimal(38,6)`. The rules for the precision of the result of decimal division are in this link http://msdn.microsoft.com/en-us/library/ms190476.aspx result precision= `p1 - s1 + s2 + max(6, s1 + p2 + 1)` result scale = `max(6, s1 + p2 + 1)` But "when a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated" – Martin Smith Mar 22 '11 at 00:58
  • Thanks martin, others are working, your answer is very comprehensive – Chris Mar 22 '11 at 02:02
3

This is a bit workaround, but I think it's worth a note.

select ((6.71*10000)/(3.41*10000)) as NewNumber

This query:

SELECT 6.71/3.41, ((6.71*1000000)/(3.41*1000000)) as NewNumber

returns:

1.967741    1.96774193548387
Piotr Salaciak
  • 1,653
  • 1
  • 15
  • 28
3

The literal 6.71 is treated as a numeric which has a fixed precision. Since you're doing division, you're changing the number of decimal places, which is not something you want to be using when accuracy is paramount. If you want to treat your numbers like they're accurate, you need to cast the denominator in your query to be a decimal data type with a larger precision. This should work for you: 

select 6.71 / cast(3.41 as decimal(18, 8)) as NewNumber
mattmc3
  • 17,595
  • 7
  • 83
  • 103
0

Here is a simple way to include decimal points:-

SELECT 5/3                          
--1

SELECT 5/(3*1.0)                    
--1.666666

SELECT ROUND((5/(3*1.0)), 2)        
--1.670000

SELECT ROUND((5/(3*1.0)), 3)        
--1.667000

But I still couldn't find a way to remove the zeros

Asmerom Fessehaye
  • 1
  • 1