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 |
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 intHow 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 |
|
|
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. |
|
|
|
|
|
|
|