如何将输出格式更改为货币？

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

我正在尝试使以下sqlcmd输出美元货币的总金额并删除不必要的尾随0

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

2 个解决方案

#1

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

您可以将您的字段/总和转换为十进制(18,2),如下所示:

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

This will output 1.24

这将输出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);

#2

I see you already accepted an answer using CONVERT(). So you are aware, if you are in SQL 2012 or higher you can use FORMAT(), which is helpful if you also want/need to see the currency symbol.

我看到你已经使用CONVERT()接受了答案。因此,您知道,如果您使用的是SQL 2012或更高版本,则可以使用FORMAT(),如果您还需要/需要查看货币符号,这将非常有用。

Syntax

FORMAT ( value, format [, culture ] )

Example

Code:

SELECT FORMAT ( 21000.15000100, 'C', 'en-US' ) AS 'US English Result'
      ,FORMAT ( 21000.15000100, 'C', 'en-gb' ) AS 'Great Britain English Result'
      ,FORMAT ( 21000.15000100, 'C', 'de-de' ) AS 'German Result'
      ,FORMAT ( 21000.15000100, 'C', 'zh-cn' ) AS 'Chinese (Simplified PRC) Result';

Output:

Source

FORMAT (Transact-SQL)