• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1678
  • Last Modified:

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

 ( OpSegment, MAYear, MAQuarter, MAMonth, MASALES)
SELECT "dbo"."CustType"."Description" as OpSegment ,
DatePart(year,"dbo"."TimeInfo"."DATEVALUE") as MAYear ,
SUM("dbo"."orderinfo"."Extended_Price") As MASALES
"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
GROUP BY "dbo"."CustType"."Description" ,

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.
  • 2
1 Solution
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 AnderssonCommented:
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...
rianAuthor Commented:
But there is still no way to add the comma's and the dollar sign to a money value.
Göran AnderssonCommented:
The commas can be handled by convert, but the dollar sign has to be added as a string:

'$'+convert(varchar,cast(Account as money),1)

I guess that MS-SQL has realised that there are other currencies out there... ;)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now