Formatting money for a report...

Question

Post reply

Formatting money for a report...

Mike Menser

Hall of Fame

Points: 3679
More actions
February 25, 2009 at 9:12 am

#206335

Hello! I need a field formatted for a report. The exact format should be: leading dollar sign, contains commas (US Currency) but does not contain the decimal places. The column is formatted as money, and the visual studio report field is formatted for money also therefore it shows: $9,999,999.99. I would just need to remove the decimal places from that. I cannot do it using SUBTRING, because it is money, so I had to convert it first. This is what I tried:
SELECT '$' + SUBSTRING(convert(varchar(255),column_name), 1, LEN(column_name)-3) as investment
from tablename
This will give me $9999999
It removes the decimal places, however I lose the commas. Is there anyway to have the best of both worlds?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

ok, you'd have to tweak this to become a function i guess, but this seems to work.

you'll have to have a Numbers or Tally table to use this, but it works for any size number I've tried so far:

[font="Courier New"]

DECLARE @TheNumber NUMERIC (24,4), @TheNumberVarChar VARCHAR (20),@formatted VARCHAR(255)

SET @TheNumber = 1123456789012.23 -- big dollar amount1,123,456,789,012.23

SET @formatted=REVERSE(SUBSTRING(CONVERT(VARCHAR(255),@TheNumber),1,CHARINDEX('.',CONVERT(VARCHAR(255),@TheNumber)) -1))

SET @TheNumberVarChar = ''

SELECT @TheNumberVarChar = @TheNumberVarChar +

SUBSTRING(REPLACE(CAST(@formatted AS VARCHAR (20)), ',', ''), number, 3) +

CASE WHEN LEN(SUBSTRING(REPLACE(CAST(@formatted AS VARCHAR (20)), ',', ''), number, 3)) = 3 THEN ',' ELSE '' END

FROM Numbers

WHERE number <= LEN(REPLACE(CAST(@formatted AS VARCHAR (20)), ',', ''))

AND (number + 2) % 3 = 0

SELECT '$' + REVERSE((@TheNumberVarChar))

--results:$1,123,456,789,012[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GSquared SSC Guru Points: 260824 More actions · Answer 2

I think you can configure the report field to remove decimal places. Could be wrong, but I think I remember doing that at one point.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Mike Menser Hall of Fame Points: 3679 More actions · Answer 3

I thought so as well when I told the end user it could be done easily, but then when it was time, I only had one option. Format value of "c" for currency, and it's format is $1,234.56.