带有逗号和句点的SQL Server 2005货币格式

Is there a way to cast a money field in SQL Server 2005 to format it

有没有办法在SQL Server 2005中转换货币字段来格式化它

Ex) the field contains:

例)该字段包含:

99966.00

and we want to return it in this format: $99,966.00

我们想以这种格式返回它:$ 99,966.00

4 个解决方案

#1

'$' + convert(varchar,cast(SalesProducts.Price as money),-1) as Price

This works

#2

try this it works for SQL Server 2008 and below (2012 have already a FORMAT() function that you can use)

试试这个适用于SQL Server 2008及以下版本(2012已经有一个你可以使用的FORMAT()函数)

this will only works for data type Money and SmallMoney

这只适用于Money和SmallMoney数据类型

declare @v money -- or smallmoney
set @v = 1000.0123
select convert(varchar(25), @v, 0)
select convert(varchar(25), @v, 1)
select convert(varchar(25), @v, 2)
select convert(varchar(25), @v, 126)

select '$' + convert(varchar(25), @v, 0)
select '$' + convert(varchar(25), @v, 1)
select '$' + convert(varchar(25), @v, 2)
select '$' + convert(varchar(25), @v, 126)

HOPE THIS HELP!

希望这个帮助!

#3

What about CHF 9'666,00 or £99,966.00?

9,966,00瑞士法郎或99,966.00瑞士法郎怎么样?

"Currency" is a number in the database: not a regional or locale setting

“Currency”是数据库中的数字:不是区域设置或区域设置

#4

Try

declare @d float = 34.5

select format(@d, '0.0')  -- 34.5
select format(@d, '00')  -- 35
select format(@d, '0000') -- 0035
select format(@d, '0000.00') -- 0034.50

Good?

#1

'$' + convert(varchar,cast(SalesProducts.Price as money),-1) as Price