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.
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 signListed = 81781101.10and I would like the result after format = $81,781,101.10Example2: Display with no decimal, and no dollar signListed = 5712678and I would like to get after format = $5,712,678.00Thanks 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 handDECLARE @money moneySET @money = 123456789.12SELECT '$'+ CONVERT(varchar(20),@money, 1)Jim |
|
|
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 signListed = 81781101.10and I would like the result after format = $81,781,101.10Example2: Display with no decimal, and no dollar signListed = 5712678and I would like to get after format = $5,712,678.00Thanks a bunch!
Where do you want to shoe formatted currency values?MadhivananFailing to plan is Planning to fail |
|
|
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 signListed = 81781101.10and I would like the result after format = $81,781,101.10Example2: Display with no decimal, and no dollar signListed = 5712678and I would like to get after format = $5,712,678.00Thanks a bunch!
Where do you want to shoe formatted currency values?MadhivananFailing 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, |
|
|
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 |
|
|
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, |
|
|
jasminecameron
Starting Member
1 Post |
Posted - 2008-10-07 : 01:50:26
|
Consider the following SQL request:USE NorthwindGOSELECT 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 NorthwindGOSELECT 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] |
|
|
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? |
|
|
maunishq
Yak Posting Veteran
71 Posts |
Posted - 2014-02-25 : 12:15:37
|
'$'+CAST(CONVERT(DECIMAL(30,2),column_name) AS varchar(20))!_(M)_! |
|
|
|
|
|
|
|