SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to Format Currency
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ntn104
Posting Yak Master

175 Posts

Posted - 08/12/2008 :  09:19:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 08/12/2008 :  09:34:02  Show Profile  Reply with Quote
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

India
22761 Posts

Posted - 08/12/2008 :  09:45:25  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 - 08/12/2008 :  09:58:59  Show Profile  Reply with Quote
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

United Kingdom
530 Posts

Posted - 08/12/2008 :  10:17:34  Show Profile  Reply with Quote
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

Edited by - NeilG on 08/12/2008 10:18:52
Go to Top of Page

ntn104
Posting Yak Master

175 Posts

Posted - 08/12/2008 :  10:36:27  Show Profile  Reply with Quote
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 Posts

Posted - 10/07/2008 :  01:50:26  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 10/07/2008 :  02:16:20  Show Profile  Reply with Quote
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

Canada
71 Posts

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



!_(M)_!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000