SSE2008 money Type

[Gene Wirchenko]

Dear SQLers:

According to Microsoft
http://msdn.microsoft.com/en-us/library/aa258271%28v=sql.80%29.aspx
money has four decimal digits. Unfortunately, if I use print to check
things, I sometimes do not get the four digits displayed as in:

print 12.345; -- prints 12.345
print convert(money,12.345); -- prints 12.35
print 12.35-convert(money,12.345); -- prints 0.0050
print convert(money,12.35)-convert(money,12.345); -- prints 0.01
print 12.345+convert(money,12.345)-12.345; -- prints 12.3450

The precision is apparently there per the third statement, but
how do I force it? I have tried variations with the second statement,
but I have been unable to get it to output 12.345. How is it that the
third statement gives four decimal digits, but the second and fourth
do not? The fifth statement is just plain weird.

I have no trouble with selects.

What am I missing, please?

Sincerely,

Gene Wirchenko

[rpresser]

Post by Gene Wirchenko
I have no trouble with selects.
What am I missing, please?

http://msdn.microsoft.com/en-us/library/ms176047.aspx

PRINT msg_str | @local_variable | string_expr

The argument to PRINT must be a string expression (char, nchar, varchar, or nvarchar), or it must be able to be implicitly converted to those data types.

Implicit or explicit conversion from MONEY to VARCHAR assumes two decimal places, even though money is 4 decimals internally. But conversion from decimal(10,4) to varchar uses 4 decimal places. When computing your last line, the numeric constants combined with the explicit conversion to money end up with a data type of decimal(10,4). (The 10 is a guess, but the 4 is proven.)

See also CAST AND CONVERT in BOL, under the section "money and smallmoney Styles"
http://msdn.microsoft.com/en-us/library/ms187928.aspx

Post by Gene Wirchenko
0 (default)
No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.

[Bob Barrows]

Post by Gene Wirchenko
According to Microsoft
http://msdn.microsoft.com/en-us/library/aa258271%28v=sql.80%29.aspx
money has four decimal digits. Unfortunately, if I use print to check

You're being caught by implicit conversions to varchar.

Post by Gene Wirchenko
print 12.345; -- prints 12.345

Implicit conversion to varchar using the default style setting for float or
real data (0), resulting in a maximum of 6 decimal places, using scientific
notation when appropriate.

Post by Gene Wirchenko
print convert(money,12.345); -- prints 12.35

Implicit conversion to varchar, using the default style setting for
money/smallmoney (0 - no commas for thousands separators and 2 decimal
places)

Post by Gene Wirchenko
print 12.35-convert(money,12.345); -- prints 0.0050

Same as the first, with the wrinkle that arithmetic using money and real
values results in a real value.

Post by Gene Wirchenko
print convert(money,12.35)-convert(money,12.345); -- prints 0.01

Same as second - answer rounded to two places

Post by Gene Wirchenko
print 12.345+convert(money,12.345)-12.345; -- prints 12.3450

Sane as third

Post by Gene Wirchenko
The precision is apparently there per the third statement, but
how do I force it? I have tried variations with the second statement,
but I have been unable to get it to output 12.345. How is it that the
third statement gives four decimal digits, but the second and fourth
do not? The fifth statement is just plain weird.

Look up the Style arguments for CONVERT() in BOL. This can all be avoided by
using explicit conversions.

[Erland Sommarskog]

Post by Gene Wirchenko
print 12.345; -- prints 12.345
print convert(money,12.345); -- prints 12.35
print 12.35-convert(money,12.345); -- prints 0.0050
print convert(money,12.35)-convert(money,12.345); -- prints 0.01
print 12.345+convert(money,12.345)-12.345; -- prints 12.3450
The precision is apparently there per the third statement, but
how do I force it? I have tried variations with the second statement,
but I have been unable to get it to output 12.345. How is it that the
third statement gives four decimal digits, but the second and fourth
do not? The fifth statement is just plain weird.

You are confusing precision with print format and the conversion to varchar.

Apparently money is converted to varchar without display of trailing zeroes.
The decimal data type is not. And when you add a number like 12.35 you are
in decimal land, since the data type of 12.35 is decimal(5,2).

Use the str() function to get trailing zeroes:

declare @m money = 1.234
select str(@m, 6, 4)

[rpresser]

Post by Erland Sommarskog
Apparently money is converted to varchar without display of trailing zeroes.

Not so. It's converted to two digits flat unless you provide a format specifier.

PRINT CONVERT(MONEY, 2) -- prints 2.00
PRINT CONVERT(MONEY, 2.3) -- prints 2.30
PRINT CONVERT(VARCHAR, CONVERT(MONEY,2.3), 2) -- prints 2.3000