How SQL Server Knows What Accuracy to Use for Money

Question

How does SQL Server know to get these values ​​this way?

Key someMoney ----------- --------------------- 1 5.00 2 5.002 3 5.0001 

Basically, I am wondering how to find out how many decimal places exist without a lot of performance.

I want to receive

 Key someMoney places ----------- --------------------- ---------- 1 5.00 2 2 5.002 3 3 5.0001 4 

Answer 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 a decimal floating point or binary floating point?

Answer 2

This gives the correct results, but I'm not sure that it works well enough for you, and I have not tried it with data other than the examples you provided:

 ; 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 

Answer 3

So this is a huge ugly hack, but it will give you the meaning you are 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 

Answer 4

The client formats it. SQL Server SSMS or whatever. SQL Server returns the full monetary value in the data stream and takes 8 bytes. (Http://msdn.microsoft.com/en-us/library/cc448435.aspx). If you are converting SQL Server to varchar, it defaults to 2 decimal places

Note that the data browser does not even show the same results:

http://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​