How do I convert from a money datatype in SQL server?

Question

I have a money data type in SQL Server. How do I reformat 0.00 to 0 in my query?

Answer 1

Would casting it to int help you? Money is meant to have the decimal places...

DECLARE @test AS money
SET @test = 3
SELECT CAST(@test AS int), @test

Answer 2

First of all, you should never use the money datatype. If you do any calculations you will get truncated results. Run the following to see what I mean

DECLARE
    @mon1 MONEY,
    @mon2 MONEY,
    @mon3 MONEY,
    @mon4 MONEY,
    @num1 DECIMAL(19,4),
    @num2 DECIMAL(19,4),
    @num3 DECIMAL(19,4),
    @num4 DECIMAL(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 moneyresult,
    @num4 AS numericresult

Output: 2949.0000 2949.8525

Now to answer your question (it was a little vague), the money datatype always has two places after the decimal point. Use the integer datatype if you don't want the fractional part or convert to int.

Perhaps you want to use the decimal or numeric datatype?

Answer 3

This looks like a formating issue to me.
As far as SQL Server's money type is concerned 0 == 0.00

If you're trying to display 0 in say c# rather then 0.00 you should convert it to a string, and format it as you want. (or truncate it.)

Answer 4

Normal money conversions will preserve individual pennies:

SELECT convert(varchar(30), moneyfield, 1)

The last parameter decides what the output format looks like:

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.

1 Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.

2 No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.

If you want to truncate the pennies, and count in pounds, you can use rounding to the nearest pound, floor to the lowest whole pound, or ceiling to round up the pounds:

SELECT convert(int, round(moneyfield, 0))
SELECT convert(int, floor(moneyfield))
SELECT convert(int, ceiling(moneyfield))

Answer 5

It seems despite the intrinsic limitations of the money datatype, if you're already using it (or have inherited it as I have) the answer to your question is, use DECIMAL.