John K
John K

Reputation: 33

How to add '$' after '-' in SQL Query when formatting for currency

The following SQL will render the value as show here:$-541.42

'$'+CONVERT(VARCHAR, CONVERT(MONEY, MYCOLUMN), 1)

How do I have the format as shown here: -$541.42

Upvotes: 1

Views: 82

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Here is a cute alternative that doesn't use case:

replace('$' + CONVERT(VARCHAR(255), CONVERT(MONEY, MYCOLUMN), 1), '$-', '-$')

Note: You should always include a length when using varchar in SQL Server. The default length varies by context and might not be sufficient

Upvotes: 2

Abdul Rasheed
Abdul Rasheed

Reputation: 6739

Try like this

SELECT (CASE WHEN @val < 0 THEN '-' ELSE '' END)+
    '$'+CONVERT(VARCHAR, ABS(CONVERT(MONEY, @val)), 1)

Upvotes: 1

Related Questions