How does SQL Server know what precision to use for money
14,645
Solution 1
Money has 4 decimal places....it's a fixed-point data type.
- http://msdn.microsoft.com/en-us/library/ms179882.aspx
- Is SQL Server 'MONEY' data type a decimal floating point or binary floating point?
Solution 2
This produces the correct results, but I'm not sure if it performs well enough for you and I haven't tried it with data other than the examples you listed:
;
with money_cte ([Key], [someMoney])
as
(
select 1, cast(5.00 as money)
union
select 2, cast(5.002 as money)
union
select 3, cast(5.0001 as money)
)
select [Key], [someMoney], abs(floor(log10([someMoney] - round([someMoney], 0, 1)))) as places
from money_cte
where [someMoney] - round([someMoney], 0, 1) <> 0
union
select [Key], [someMoney], 2 as places
from money_cte
where [someMoney] - round([someMoney], 0, 1) = 0
Author by
Daniel A. White
I'm a Software Engineer in Indianapolis, Indiana who is #SOreadytohelp! Member of the .NET Foundation.
Updated on June 29, 2022Comments
-
Daniel A. White about 2 years
How does SQL Server know to retrieve these values this way?
Key someMoney ----------- --------------------- 1 5.00 2 5.002 3 5.0001
Basically, I'm wondering how to know how many decimal places there are without much of a performance hit.
I want to get
Key someMoney places ----------- --------------------- ---------- 1 5.00 2 2 5.002 3 3 5.0001 4
-
Andrew Barber almost 13 yearsFour are being used. If you insert the value 1, mssql stores 1.0000
-
Daniel A. White almost 13 yearsHow does sql server then return it with only 2 decimal places?
-
Chains almost 13 yearsSo I'm researching this, and came across the concept of VARDECIMAL storage format. (It's a table property.) I initially thought ah-ha! But when I looked at my database, I see it's turned-off and I still get the same display you do. So I don't think this is the answer, but might be worth reading: mssqltips.com/sqlservertip/1614/…