displaying smallmoney to 2 decimal places

Heres an example of a query with the display
SELECT * FROM STOCKks WHERE Price < Cost; Both price and cost are
smallmoney datatypes. They display with 4 digits to the left of decimal pt.
How do I set it to display only 2 digits for both colums?
THanks, Kim
On Fri, 28 Oct 2005 18:05:01 -0700, Kim wrote:

>Heres an example of a query with the display
>SELECT * FROM STOCKks WHERE Price < Cost; Both price and cost are
>smallmoney datatypes. They display with 4 digits to the left of decimal pt.
>How do I set it to display only 2 digits for both colums?
>THanks, Kim
Hi Kim,
The best advise is to:
a) do formatting in the front end, not at the server, and
b) avoid the use of money and smallmoney datatype, becuase they are
proprietary and non-portable AND because they suffer from some strange
rounding issues. Use DECIMAL (17,2) instead.
If you must use money and must do the formatting at the server, then
SELECT CAST (Price AS DECIMAL(17,2))
will work.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Displaying Small Money

Hi,
How do I remove the decimal places from this to show just whole pounds
?
SELECT '=A3' + convert(varchar(30),PotentialRevenue,3) as [Potential
Revenue]
What is the purpose of the small money data type ? - its seems to have
no features for storing/displaying financial data that cant be done
with other data types.> How do I remove the decimal places from this to show just whole pounds?
The formatting of numeric values is controlled by your client
application, not by SQL Server and that's how it should be. ROUND the
value on the server if you need to but do presentation client-side.

> What is the purpose of the small money data type ?
Good question. In some cases the money types may save you 1 byte over a
DECIMAL column. However, given the rounding errors caused when you
divide or multiply the money types I would always avoid them unless
compelled to use them. I can't think of a good reason to use MONEY or
SMALLMONEY.
David Portas
SQL Server MVP
--|||> DECIMAL column. However, given the rounding errors caused when you
> divide or multiply the money types I would always avoid them unless
> compelled to use them. I can't think of a good reason to use MONEY or
> SMALLMONEY.
Nor can I.
For the OP's benefit: http://www.aspfaq.com/2503