Formating a number to "$#,##0;($#,##0)"

[Ginka]

I have in a stored procedure a query that returns a number, but I need it formatted like if it where money.

How can I do that?

something like this:

SELECT MyField
FROM AnyTable

MyField is the result of some real and integer calculations and I want it like this $1,256.23

 

[SQLBill]

Try this:

SELECT '$' + CONVERT(VARCHAR(10),(CONVERT(MONEY, 125623, 1)))

The CONVERT(MONEY, <value>, 1) uses the style 1 to add the decimal and commas. You have to then make it a string and concatenate the dollar sign.

Refer to the BOL for CONVERT.

-SQLBill

Posting advice: FAQ481-4875

 

[Ginka]

Thanks SQLBill, it is working with the dollar sign, but the comma is missing.

Any other suggestion?

 

[SQLBill]

Okay, after looking at the BOL again...you have to start with a MONEY value.

DECLARE @myval MONEY
SET @myval = 123456
SELECT '$' + CONVERT(VARCHAR(20), @myval, 1)

-SQLBill

Posting advice: FAQ481-4875