asked on

Converting float to currency

Is there a way to convert float to currency within a SQL query similar to the format function in VB where it adds a $ and puts commas in the correct places.

I have a table which has MASUM as a float and MASTRSUM as char(20).

I perform a query

INSERT INTO CT_SALES
( OpSegment, MAYear, MAQuarter, MAMonth, MASALES)
SELECT "dbo"."CustType"."Description" as OpSegment ,
DatePart(year,"dbo"."TimeInfo"."DATEVALUE") as MAYear ,
NULL,
NULL,
SUM("dbo"."orderinfo"."Extended_Price") As MASALES
FROM
"dbo"."orderinfo", "dbo"."CustInfo", "dbo"."CustType", "dbo"."TimeInfo"
WHERE ("dbo"."CustInfo"."MATYPE"="dbo"."CustType"."MATYPE") AND
("dbo"."CustInfo"."DCODE"="dbo"."orderinfo"."DCODE") AND
("dbo"."CustInfo"."CUSNR"="dbo"."orderinfo"."CUSNR") AND
("dbo"."orderinfo"."DDATE"="dbo"."TimeInfo"."DDATE")
GROUP BY "dbo"."CustType"."Description" ,
DatePart(year,"dbo"."TimeInfo"."DATEVALUE")

I would like in this query also add information in the MASTRSUM field which is in currency format. Currently I use a VB program to perform the FORMAT conversion.

TheSpirit

Take a look at convert in Books on line, this may provide the info you need, but I don't think it will start adding $ symbols in the output.

Göran Andersson

You could use:

cast(cast(MASUM as money) as varchar)

It doesn't add any currency symbol, though (at least not on my computer). It only rounds the value to two decimals...

rian

ASKER

But there is still no way to add the comma's and the dollar sign to a money value.

ASKER CERTIFIED SOLUTION

Göran Andersson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial