How do I change output format to currency?

Question

I'm trying to make the following sqlcmd output the totaled amount in USD Currency and remove unnecessary trailing 0's

declare @today datetime = '2015-5-2'  set nocount on;select [Day Total]  = sum(case when AccountingDate > dateadd(DAY, -1, @today) then [TotalJackpotAmount] else 0 end),        [Week Total] = sum(case when AccountingDate > dateadd(WEEK, -1, @today) then [TotalJackpotAmount] else 0 end),        [Month Total] = sum(case when AccountingDate > dateadd(MONTH, -1, @today) then [TotalJackpotAmount] else 0 end),         [Year Total] = sum([TotalJackpotAmount]) from   [Accounting].[dbo].[HandPay] where [AccountingDate] <= @today and [AccountingDate] > dateadd(year, -1, @today);" -o \\support\c$\inetpub\wwwroot\handpaytest\index.html -h-1 -s "," -w 700 -W

Answer 1

You could just convert your field/sum to decimal(18,2) like this:

select convert(decimal(18,2),1.235) 

This will output 1.24

Your query will then look like this:

declare @today datetime = '2015-5-2'  
set nocount on;
select 
[Day Total]  = convert(decimal(18,2),sum(case when AccountingDate > dateadd(DAY, -1, @today) then [TotalJackpotAmount] else 0 end)),        
[Week Total] = convert(decimal(18,2),sum(case when AccountingDate > dateadd(WEEK, -1, @today) then [TotalJackpotAmount] else 0 end)),        
[Month Total] = convert(decimal(18,2),sum(case when AccountingDate > dateadd(MONTH, -1, @today) then [TotalJackpotAmount] else 0 end)),         
[Year Total] = convert(decimal(18,2),sum([TotalJackpotAmount]) )
from   [Accounting].[dbo].[HandPay] 
where [AccountingDate] <= @today and [AccountingDate] > dateadd(year, -1, @today);