将货币格式化为百万美元SQL Server

dz6r00yl 于 2022-12-10 发布在 SQL Server

关注(0)|答案(4)|浏览(168)

Can anyone help me format my dollars data into millions of dollars for SQL Server?

3,000,000

--> $3M

I have this but it's not working

SELECT     '$' + SUM(Sales_Information.Sales_in_Dollars / 1000000) 
                      AS [Sales in Millions]

Doing this gives me #Error

format(SUM(Sales_Information.Sales_in_Dollars / 1000000)

4条答案

The FORMAT function has a way of trimming the thousands
each comma reduces the displayed value by 1000
e.g.

select format(3000000,'$0,,,.000B')
select format(3000000,'$0,,M')
select format(3000000,'$0,K')

(note that I had to use decimals to show 3 million in Billions)
Output:
$0.003B
$3M
$3000K

试试这个...

SELECT '$' + CONVERT(VARCHAR(100),CAST(3000000 AS MONEY),1)

RESULT:  $3,000,000.00

用1000000除你的货币，然后在它的后面加上一个“M”

只需根据您的要求进行一些编辑：

CONCAT('$', ROUND(SUM(Sales_Information.Sales_in_Dollars) / 1000000), ' million)