Output formatting

Question

Post reply

Output formatting

M Jones

SSCommitted

Points: 1711
More actions
November 15, 2010 at 1:31 pm

#231338

I have a table, table1, that has several currency fields (type is money).
If I use this : SELECT num1 FROM table1 the output looks like :1234567.0000
using SELECT ROUND(num1,0,0) FROM table1 the output looks like 1234567
How can I get the output to display like this: 1,234,567 :unsure:
~mj

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

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

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 1

Use CONVERT:

SELECT LEFT(CONVERT(varchar(16), amount, 1), LEN(CONVERT(varchar(16), amount, 1)) -3) AS Amount

FROM (

SELECT CAST(1234567.8901 AS money) AS amount

UNION ALL

SELECT CAST(1234567890.1234 AS money)

) AS inlineData

Scott Pletcher, SQL Server MVP 2008-2010

Steve Jones - SSC Editor SSC Guru Points: 736254 More actions · Answer 2

CAST and CONVERT http://msdn.microsoft.com/en-us/library/ms187928%28v=SQL.100%29.aspx

WayneS SSC Guru Points: 95460 More actions · Answer 3

mj,

You're close, but to finish it up you need to utilize the CONVERT function, with the appropriate style for money datatypes.

DECLARE @test-2 TABLE (MoneyField money);

INSERT INTO @test-2 VALUES (1234567.5555);

SELECT REPLACE(CONVERT(varchar(20), CONVERT(money, ROUND(MoneyField,0,1)), 1), '.00',''), -- truncate original value

REPLACE(CONVERT(varchar(20), CONVERT(money, ROUND(MoneyField,0,0)), 1), '.00','') -- round the original value

FROM @test-2;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2