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
 General SQL Server Forums
 New to SQL Server Programming
 Rounding to 2 decimals with $ sign
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rock_query
Yak Posting Veteran

52 Posts

Posted - 06/05/2013 :  18:13:19  Show Profile  Reply with Quote
I am using the AdventureWorks2012 database. Here is my code:

SELECT SalesOrderID, SUM(UnitPrice) AS Price
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID

This 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
Flowing Fount of Yak Knowledge

USA
1600 Posts

Posted - 06/05/2013 :  19:43:55  Show Profile  Reply with Quote
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)
Go to Top of Page

Rock_query
Yak Posting Veteran

52 Posts

Posted - 06/18/2013 :  13:10:17  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3326 Posts

Posted - 06/18/2013 :  14:09:27  Show Profile  Reply with Quote
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.
Go to Top of Page

shan007
Starting Member

USA
17 Posts

Posted - 06/18/2013 :  14:23:34  Show Profile  Reply with Quote
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 Price
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID

==============================
I'm here to learn new things everyday..

Edited by - shan007 on 06/18/2013 14:26:21
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.05 seconds. Powered By: Snitz Forums 2000