sqlmoney or decimal

[Ados]

Hi friends,

Sorry for my not very good English.

I am new in sql server.

I am using sqlmoney to represent the price. First of all I am not sure is it
good idea or I have to use decimal?

And my second question is haw to cut the last 2 digits of sqlmoney (I mean
for example 12345.1200 to become 12345.12. Of course I can do this later in
my program, but it is better if I am doing it in SP)

Thanks in advance.

Special Thanks for Hugo Kornelis and John Bell for their big help.

[Hugo Kornelis]

On Tue, 24 May 2005 22:53:39 +0100, Ados wrote:

>Hi friends,
>
>Sorry for my not very good English.
>
>I am new in sql server.
>
>I am using sqlmoney to represent the price. First of all I am not sure is it
>good idea or I have to use decimal?

Hi Ados,

Use decimal. The money and smallmoney types are proprietary. There's
nothing wrong with using proprietary features if they have an added
value, but this one hasn't - so use the more portable standard datatypes
instead.

Also, I've read that the money datatypes may exhibit some strange
results from improper rounding (unfortuantely, I have no link
available).

>And my second question is haw to cut the last 2 digits of sqlmoney (I mean
>for example 12345.1200 to become 12345.12. Of course I can do this later in
>my program, but it is better if I am doing it in SP)

No, it's better to do all presentational issues in the front end. Pass
parameters as native types (decimal as decimal, datetime as datetime,
int as int, ...) and add formatting code in the front-end.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[David Portas]

Here's an example of the rounding problems Hugo mentioned. He's right. Avoid
MONEY / SMALLMONEY.

DECLARE
@mon1 MONEY,
@mon2 MONEY,
@mon3 MONEY,
@mon4 MONEY,
@num1 NUMERIC(19,4),
@num2 NUMERIC(19,4),
@num3 NUMERIC(19,4),
@num4 NUMERIC(19,4)

SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000

SET @mon4 = @mon1/@mon2*@mon3
SET @num4 = @num1/@num2*@num3

SELECT @mon4 AS money_result,
@num4 AS numeric_result

Result:

money_result numeric_result
--------------------- ---------------------
2949.0000 2949.8525

(1 row(s) affected)

--
David Portas
SQL Server MVP
--