CODEWITHSUNDEEP
sql-servercurrency
senthil
senthil

Reputation:

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

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

Upvotes: 23

Views: 138144

Answers (9)

Dan Field
Dan Field

Reputation: 21641

I had this issue as well, and was tripped up for a while on it. I wanted to display 0.00 as 0 and otherwise keep the decimal point. The following didn't work:

CASE WHEN Amount= 0 THEN CONVERT(VARCHAR(30), Amount, 1) ELSE Amount END

Because the resulting column was forced to be a MONEY column. To resolve it, the following worked

CASE WHEN Amount= 0 THEN CONVERT(VARCHAR(30), '0', 1) ELSE CONVERT (VARCHAR(30), Amount, 1) END

This mattered because my final destination column was a VARCHAR(30), and the consumers of that column would error out if an amount was '0.00' instead of '0'.

Upvotes: 1

liang
liang

Reputation: 1611

I found this approach direct and useful.

CONVERT(VARCHAR(10), CONVERT(MONEY, fieldname)) AS PRICE

Upvotes: 1

gdfgdgfdfg
gdfgdgfdfg

Reputation: 11

You can try like this:

SELECT PARSENAME('$'+ Convert(varchar,Convert(money,@MoneyValue),1),2)

Upvotes: 1

jero
jero

Reputation: 543

you could either use 

SELECT PARSENAME('$'+ Convert(varchar,Convert(money,@MoneyValue),1),2)

or 

SELECT CurrencyNoDecimals = '$'+ LEFT( CONVERT(varchar, @MoneyValue,1),    
       LEN (CONVERT(varchar, @MoneyValue,1)) - 2)

Upvotes: 0

Stuart Helwig
Stuart Helwig

Reputation: 9454

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.

Upvotes: 1

Jonathan
Jonathan

Reputation: 26609

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))

Upvotes: 34

SQLMenace
SQLMenace

Reputation: 134961

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?

Upvotes: 3

Bravax
Bravax

Reputation: 10483

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.)

Upvotes: 0

Tina Orooji
Tina Orooji

Reputation: 1840

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

Upvotes: 3

Related Questions