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
 General SQL Server Forums
 New to SQL Server Programming
 Convert the column to money

Author  Topic 

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2014-03-20 : 08:49:22
Hi,

cast(round([YearlyProfit]-[MonthlyProfit],0)as int),
cast(round(isnull(sum(SalesProfit]),0),0) as int

How can I convert these columns to money so that I can display the result with comma included.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-20 : 10:44:32
The expert recommendation is that you should not do the formatting as you have described in SQL Server, and that you should do that in the presentation layer (reporting services, aspx page etc.). But, if you must, you will have to format it as a string like in the example below:
CONVERT(VARCHAR(32),CAST(round([YearlyProfit]-[MonthlyProfit],0) as MONEY),1)
If you want to discard the trailing ".00", remove it like this:
REPLACE( CONVERT(VARCHAR(32),CAST(round([YearlyProfit]-[MonthlyProfit],0) as MONEY),1), '.00', '')

As you can see, it is not pretty. Hence the advice to do it in presentation layer. For example, in SSRS, you can specify the formatting as "C" will give you the formatting you are looking for. See here http://msdn.microsoft.com/en-us/library/ms252080%28VS.80%29.aspx
Go to Top of Page

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2014-03-20 : 13:14:22
quote:
Originally posted by James K

The expert recommendation is that you should not do the formatting as you have described in SQL Server, and that you should do that in the presentation layer (reporting services, aspx page etc.). But, if you must, you will have to format it as a string like in the example below:
CONVERT(VARCHAR(32),CAST(round([YearlyProfit]-[MonthlyProfit],0) as MONEY),1)
If you want to discard the trailing ".00", remove it like this:
REPLACE( CONVERT(VARCHAR(32),CAST(round([YearlyProfit]-[MonthlyProfit],0) as MONEY),1), '.00', '')

As you can see, it is not pretty. Hence the advice to do it in presentation layer. For example, in SSRS, you can specify the formatting as "C" will give you the formatting you are looking for. See here http://msdn.microsoft.com/en-us/library/ms252080%28VS.80%29.aspx



Thanks a lot James. That worked like a charm.
Go to Top of Page
   

- Advertisement -