Need to convert Varchar to Money with symbols

Question

Post reply

Need to convert Varchar to Money with symbols

Sql Student-446896

Default port

Points: 1425
More actions
January 16, 2013 at 9:48 am

#269366

Hi,

I have a variable @amt = 5000 or @amt = -5000. I use the following code to conver to money. However, i am not able to get a negetive symbol '-' to show a negetive amount.

Select '$ + Convert (Varchar, CAST(Amount as Money), -1)

Result:

$5,000

$-5,000 (this should be -$5,000).

How do i do this?

Viewing 7 posts - 1 through 6 (of 6 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Maybe this?

DECLARE @amt VARCHAR(10) = '-5000';



SELECT  CASE WHEN LEFT(@amt, 1) = '-' THEN '-'

             ELSE ''

        END + '$' + CAST(ABS(CAST(@amt AS MONEY)) AS VARCHAR(100));

GO

DECLARE @amt VARCHAR(10) = '5000';

SELECT  CASE WHEN LEFT(@amt, 1) = '-' THEN '-'

             ELSE ''

END + '$' + CAST(ABS(CAST(@amt AS MONEY)) AS VARCHAR(100));

- 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

GSquared SSC Guru Points: 260824 More actions · Answer 2

But, really, that kind of formatting should be done in the presentation layer, not in a query. Should be formatted by the report/web page/application.

- 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

SQLRNNR SSC Guru Points: 281324 More actions · Answer 3

SQLRNNR

SSC Guru

Points: 281324

January 16, 2013 at 10:37 am

#1577957



DECLARE @amt VARCHAR(20) = '5000'

SELECT REPLACE('$' + CONVERT(VARCHAR(20),(CAST(@amt AS MONEY))),'$-','-$')

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

GSquared SSC Guru Points: 260824 More actions · Answer 4

The Replace() version is better than the Case version. But you should still seriously consider moving that kind of thing to the presentation layer of the application.

- 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

SQLRNNR SSC Guru Points: 281324 More actions · Answer 5

GSquared (1/16/2013)

The Replace() version is better than the Case version. But you should still seriously consider moving that kind of thing to the presentation layer of the application.

I agree this should be a presentation thing and not so much done via tsql.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 6

I agree things like this are best left to a presentation layer but the appetite for features like this is obviously present as Microsoft added FORMAT() to T-SQL in SQL 2012 for just this kind of thing:

http://msdn.microsoft.com/en-us/library/hh213505.aspx

USE AdventureWorks2012;



GO

SELECT TOP (5)

        EndOfDayRate,

        FORMAT(EndOfDayRate, 'C', 'en-us') AS 'Currency Format'

FROM    Sales.CurrencyRate

ORDER BY CurrencyRateID;

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato