change format of concatenated columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

change format of concatenated columns

Author

Topic

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-03-23 : 20:47:13

Is there a way to have my output return this, "$-2,778.90 $$$ IN NETWORK DISCOUNT", instead of what I am currently getting which is this "-2779 $$$ IN NETWORK DISCOUNT" using:

LTRIM(STR(ar_billtrans_payadj.amount)+ ' ' + '$$$' + ' ' + ut_journalcode.[description]) AS PPmtTypeDesc

ar_billtrans_payadj.amount datatype is decimal
ut_journalcod.[description] datatype is varchar

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-23 : 21:07:12

You can convert ar_billtrans_payadj.amount to varchar with convert or cast function.

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-03-23 : 21:17:00

I'm getting closer with this, is there any way to multiply ar_billtrans_payadj.amount * -1 before the convert to return
this "$-2778.90 $$$ IN NETWORK DISCOUNT"?
When I try to add the mulitply I get this error, "Syntax error converting the varchar value '$' to a column of data type int."
'$'+ convert(varchar,ar_billtrans_payadj.amount) * -1 + ' ' + '$$$' + ' ' + ut_journalcode.[description]

smorty44
Yak Posting Veteran

93 Posts

Posted - 2008-03-23 : 21:18:51

I got it, and thank you for the help.
solution:
'$'+ convert(varchar,ar_billtrans_payadj.amount * -1) + ' ' + '$$$' + ' ' + ut_journalcode.[description]
returns: $-2778.90 $$$ IN NETWORK DISCOUNT

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-03-23 : 21:20:29

Tried this?

'$'+ convert(varchar, ar_billtrans_payadj.amount * -1) + ' ' + '$$$' + ' ' + ut_journalcode.[description]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-24 : 12:32:38

CONVERT also has a "Style" that you can specify. If ar_billtrans_payadj.amount is a MONEY or SMALLMONEY datatypes that you can format it using the "1" style to have commas every three digits to the left of the decimal point, and two digits to the right of the decimal point.

DECLARE @Amount MONEY
SET @Amount = $2778.90

SELECT '$' + CONVERT(VARCHAR(50), @Amount * -$1.0, 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources