Converting MONEY

Question

Post reply

Converting MONEY

btio_3000

SSCrazy

Points: 2662
More actions
December 23, 2020 at 7:23 pm

#3823617

Hi,
running this
declare @code VARCHAR( 50 ) = '748.019876';
select
[code1] = @code,
[code2] = CONVERT( MONEY, @code ),
[code3] = CONVERT( VARCHAR( 10 ), CONVERT( MONEY, @code ) )
returns
--code1 code2 code3
--748.019876 748.0199 748.02
why are [code2] and [code3] not the same?
Thanks!
January 2, 2021 at 1:40 pm

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101122 More actions · Answer 1

Two decimal places is the default when coverting from money to char. From MS docs, "CAST and CONVERT":

"

money

0 (default)

No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point

"

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

btio_3000 SSCrazy Points: 2662 More actions · Answer 2

oh... got it. Right, converting to VARCHAR does it. Got it.

Thank you

This reply was modified 4 years ago by btio_3000.

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 3

You might want to look up how the old Sybase money data types work. They round too soon and give the wrong answers. Google it. This is why competent SQL programmers for the last 30+ years have always used DECIMAL(S, P) data types instead.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 4

jcelko212 32090 wrote:

You might want to look up how the old Sybase money data types work. They round too soon and give the wrong answers. Google it. This is why competent SQL programmers for the last 30+ years have always used DECIMAL(S, P) data types instead.

In SQL Server, it's actually DECIMAL(P,S) where "S" (scale) is the number of decimal places and "P" (precision) is the total number of characters not including the decimal point. It does sound backwards but that's the way it's documented in MS documentation and does seem to follow mathematical jargon in that area.

The DECIMAL datatype has a similar problem as the MONEY datatype in that the SCALE can automatically be reduced by the system if certain conditions are met which can easily throw things like mortgage payment and interest calculation right out the window. Here's the link that describes all the gazintas for all that "gotcha"...

https://docs.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql?view=sql-server-ver15

To summarize, Granny's 4 function calculator is frequently more accurate. 😀

As a bit of a sidebar, I use the FLOAT datatype to do such mortgage calculations to avoid the "early rounding" problem that Joe speaks of and then only format the final answer using something like DECIMAL(P,S). You could also use the STR() function or the newer FORMAT() function but they are both terrible when it comes to performance (FORMAT is 43 times slower than just about any gyration you might use multiple CONVERTs for) and STR() is limited to a precision of "only) 15 digits, as well.

If you need a demo on STR() to see that type of problem, please see the following article.

https://www.sqlservercentral.com/articles/hidden-formatting-troubles-with-str-sql-spackle

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 5

btio_3000 wrote:

oh... got it. Right, converting to VARCHAR does it. Got it.
Thank you

Technically, not. You can format with VARCHAR() but you have to use the correct "style" code rather than relying on the default "style" (the 3rd parameter that's missing from the CONVERT in your code).

Please see the following on how to actually use CONVERT properly, especially when trying to display formatted MONEY values as CHAR()/VARCHAR() or (gasp) NCHAR()/NVARCHAR. Read it from head to toe and practice some of the things it talks about because CONVERT (and the related but mostly crippled CAST) functions are two of the most important functions in SQL Server and you at least need to know what it's capable of. It's long and dry reading but it's some of the most worthwhile time you'll ever spend if you work with T-SQL.

https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

btio_3000 SSCrazy Points: 2662 More actions · Answer 6

No, I got it, it's what I meant

Thanks again and Happy New Year