Change numeric column to money

Question

I have values like this in my amount column

323547.23700
12345643.34222

I want to convert that in this format

$323,547.24
$12,345,643.34

I am using this

CHAR(36)+CONVERT(VARCHAR,@Amount,1)

but this is not giving me what I want

This might give a hint what I am doing

DECLARE @amount numeric(30,5)
SET @amount = 323547.23700
DECLARE @amount1 numeric(30,5)
SET @amount1 =12345643.34222

SELECT [USD Amount]= CHAR(36)+CONVERT(VARCHAR,@amount,1)

I think that might help you: http://stackoverflow.com/questions/532351/how-do-i-convert-from-a-money-datatype-in-sql-server — Ademar, Mar 13 '12 at 10:10
See [msdn](http://msdn.microsoft.com/en-us/library/ms179882.aspx) — Alberto De Caro, Mar 13 '12 at 10:20
@ADC he is trying to keep all his decimal places, money does not do that the max there is 4 decimals — ntziolis, Mar 13 '12 at 10:28

score 1 · Accepted Answer · answered Mar 13 '12 at 10:19

1

As Ademar pointed out, you are missing the explicit length of the varchar column, this does the trick:

SELECT [USD Amount]= CHAR(36)+CONVERT(VARCHAR(30),@amount,1)

The 1 in the end should give you the desired decimal point and its values.

answered Mar 13 '12 at 10:19

ntziolis

10,091
1
34
50

score 1 · Answer 2 · answered Mar 13 '12 at 10:34

We can cast the @amount to money data type first. And then convert to varchar bt specifying the style as 1.

DECLARE @amount numeric(30,5) SET @amount = 323547.23700 DECLARE @amount1 numeric(30,5) SET @amount1 =12345643.34222

select CHAR(36)+CONVERT(varchar(20),CAST(@amount as money),1) select CHAR(36)+CONVERT(varchar(20),CAST(@amount1 as money),1)

Change numeric column to money

2 Answers2