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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to Format Currency

Author  Topic 

ntn104
Posting Yak Master

175 Posts

Posted - 2008-08-12 : 09:19:26
Hi,

Could anyone show me how to format currency in SQL statement. For example:

Example1: display as decimal, but no dollar sign
Listed = 81781101.10
and I would like the result after format = $81,781,101.10

Example2: Display with no decimal, and no dollar sign
Listed = 5712678
and I would like to get after format = $5,712,678.00

Thanks a bunch!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-08-12 : 09:34:02
I looked this up in BOL since I didn't know how right off hand

DECLARE @money money

SET @money = 123456789.12

SELECT '$'+ CONVERT(varchar(20),@money, 1)

Jim
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-12 : 09:45:25
quote:
Originally posted by ntn104

Hi,

Could anyone show me how to format currency in SQL statement. For example:

Example1: display as decimal, but no dollar sign
Listed = 81781101.10
and I would like the result after format = $81,781,101.10

Example2: Display with no decimal, and no dollar sign
Listed = 5712678
and I would like to get after format = $5,712,678.00

Thanks a bunch!


Where do you want to shoe formatted currency values?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

ntn104
Posting Yak Master

175 Posts

Posted - 2008-08-12 : 09:58:59
quote:
Originally posted by madhivanan

quote:
Originally posted by ntn104

Hi,

Could anyone show me how to format currency in SQL statement. For example:

Example1: display as decimal, but no dollar sign
Listed = 81781101.10
and I would like the result after format = $81,781,101.10

Example2: Display with no decimal, and no dollar sign
Listed = 5712678
and I would like to get after format = $5,712,678.00

Thanks a bunch!


Where do you want to shoe formatted currency values?

Madhivanan

Failing to plan is Planning to fail



In a table report, for example: In above example 1 is column 1 (as a result of my query), and example 2 is column #2 (also as a result of my query). Thanks,
Go to Top of Page

NeilG
Aged Yak Warrior

530 Posts

Posted - 2008-08-12 : 10:17:34
do you mean that you are displaying the data as an text file output of a query, if not it would be better to do the formatting in front end i.e excel

***************************
Life is for having Fun, and then a little work
Go to Top of Page

ntn104
Posting Yak Master

175 Posts

Posted - 2008-08-12 : 10:36:27
quote:
Originally posted by NeilG

do you mean that you are displaying the data as an text file output of a query, if not it would be better to do the formatting in front end i.e excel

***************************
Life is for having Fun, and then a little work



I 've been formating under Excel, but I try to find a way to include in the query so when I ran a query, it will display the report as I wanted to be...so I don't need to format in Excel or elsewhere.
thanks,
Go to Top of Page

jasminecameron
Starting Member

1 Post

Posted - 2008-10-07 : 01:50:26
Consider the following SQL request:
USE Northwind
GO
SELECT Quantity, Unitprice, Quantity * UnitPrice AS Amount
FROM [Order Details]This request results in a set that looks like Listing A (which is an abbreviated list). This delivers the correct answers yet not in the desired format. We can alter the look of the money columns, but there is a cost of sorts. For instance, the statement in Listing B delivers what we want (i.e., amounts formatted as currency).
The reader also wanted to know how to get the currency sign prepended to the amount, e.g., $1,320.00. Given the revised statement in Listing B, this is not difficult to do, as you can see here:
USE Northwind
GO
SELECT Quantity, '$' + CONVERT(varchar(12), Unitprice, 1) AS Unitprice, '$' + CONVERT(varchar(12), Quantity * UnitPrice, 1) AS Amount
FROM [Order Details]Listing C shows how this results in the desired display. I should add one additional proviso that isn't apparent. Style 1 works only on the data types money and smallmoney. Therefore, if you ever store your data in another type, then you can do a double-convert, like this:
CONVERT(varchar, CONVERT(money, YourFieldName), 1)
----------------------
Jasmine


[url=http://www.widedriven.com]Guaranteed ROI[/url]


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 02:16:20
quote:
Originally posted by ntn104

quote:
Originally posted by NeilG

do you mean that you are displaying the data as an text file output of a query, if not it would be better to do the formatting in front end i.e excel

***************************
Life is for having Fun, and then a little work



I 've been formating under Excel, but I try to find a way to include in the query so when I ran a query, it will display the report as I wanted to be...so I don't need to format in Excel or elsewhere.
thanks,


how do you export data to excel? is it direct export using export import wizard,bcp,... or do you export excel after displaying data in reporting application like sql reporting services?
Go to Top of Page

maunishq
Yak Posting Veteran

71 Posts

Posted - 2014-02-25 : 12:15:37
'$'+CAST(CONVERT(DECIMAL(30,2),column_name) AS varchar(20))



!_(M)_!
Go to Top of Page
   

- Advertisement -