How does SQL Server know what precision to use for money

Question

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

Jsut as an fyi, the money datatype is not a very accurate datatype. if you plan on doing any type of data calculations you're better off using decimal. — DForck42, Sep 01 '11 at 18:15

score 7 · Answer 1 · edited May 23 '17 at 11:55

7

Money has 4 decimal places....it's a fixed-point data type.

edited May 23 '17 at 11:55

Community

1
1

answered Sep 01 '11 at 18:08

Chains

12,541
8
45
62

Four are being used. If you insert the value 1, mssql stores 1.0000 – Andrew Barber Sep 01 '11 at 18:15
2

How does sql server then return it with only 2 decimal places? – Daniel A. White Sep 01 '11 at 18:20
So 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: http://www.mssqltips.com/sqlservertip/1614/data-compression-using-the-sql-server-vardecimal-storage-format/ – Chains Sep 01 '11 at 18:41

score 1 · Answer 2 · answered Sep 01 '11 at 20:03

So this is a huge ugly hack, but it will give you the value you're looking for...

DECLARE @TestValue MONEY
SET @TestValue = 1.001

DECLARE @TestString VARCHAR(50)
SET @TestString = REPLACE(RTRIM(REPLACE(CONVERT(VARCHAR, CONVERT(DECIMAL(10,4), @TestValue)), '0', ' ')), ' ', '0')

SELECT LEN(@TestString) - CHARINDEX('.', @TestString) AS Places

score 1 · Answer 3 · answered Sep 01 '11 at 21:05

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

score 0 · Answer 4 · edited Jan 18 '21 at 12:31

The client is formatting that. SQL Server SSMS or whatever. SQL Server is returning a full money value in the data stream and it takes a full 8 bytes. (http://msdn.microsoft.com/en-us/library/cc448435.aspx). If you have SQL Server convert to varchar, it defaults to 2 decimal places

Notice that the Stack Overflow data browser doesn't even show the same results you have:

https://data.stackexchange.com/stackoverflow/q/111288/

;
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 *
    , CONVERT(varchar, someMoney) AS varchar_version
    , CONVERT(varchar, someMoney, 2) AS varchar_version2
FROM money_cte

How does SQL Server know what precision to use for money

4 Answers4