Alex Rivera | Logout

SQL Server truncates decimal points of a newly created field in a view

Asked 2011-03-22T00:24:19.957
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?

sql-server t-sql precision
Edit
Report

1 Answer

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
answered 2011-03-22T00:36:12.690

Your Answer