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 |
|
Rock_query
Yak Posting Veteran
55 Posts |
 Posted - 2013-06-05 : 18:13:19
|
| I am using the AdventureWorks2012 database. Here is my code:SELECT SalesOrderID, SUM(UnitPrice) AS PriceFROM Sales.SalesOrderDetailGROUP BY SalesOrderIDThis runs just fine. But the alias column called Price, contains digits going out to 4 places. I want to round to the 2nd decimal place with a $ sign in front so that these numbers take on currency format.I tried using the CAST function and it is not working. |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-06-05 : 19:43:55
|
| cast(SUM(UnitPrice) as decimal(10, 2)) should get you the value. If you want to format the number with dollar signs and commas, etc., you should do that at the presentation level.=================================================May my silences become more accurate. -Theodore Roethke (1908-1963) |
 |
|
|
Rock_query
Yak Posting Veteran
55 Posts |
Posted - 2013-06-18 : 13:10:17
|
quote:
Originally posted by Bustaz Kool
cast(SUM(UnitPrice) as decimal(10, 2)) should get you the value. If you want to format the number with dollar signs and commas, etc., you should do that at the presentation level.=================================================May my silences become more accurate. -Theodore Roethke (1908-1963)
Thank you for your reply. I'm not sure what you mean by the presentation level. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-18 : 14:09:27
|
| What Bustaz Kool is suggesting is that the type of formatting (prefixing it with the currency symbol, including thousands separators etc.) are better done outside of SQL Server. For example, if you are presenting the data in a web page, .Net/C# has a number of facilities to format currencies, dates etc. to your liking, including taking care of internationalization. Similarly, if you are using SQL Server Reporting Services, that again has similar features. I am not familiar with Crystal Reports or other reporting tools, but I can't imagine that they don't have similar features. |
|
|
|
shan007
Starting Member
17 Posts |
Posted - 2013-06-18 : 14:23:34
|
| I think this should resolve your request. Let me know if it helps. I'm not sure about your purpose. Still I'd like to let you know the solution that you are expecting.SELECT SalesOrderID, '$' + CAST(ROUND(SUM(UnitPrice),2) as nvarchar(10)) AS PriceFROM Sales.SalesOrderDetailGROUP BY SalesOrderID==============================I'm here to learn new things everyday.. |
|
|
|
|
|
|
|
|
Rounding to 2 decimals with $ sign