问题:

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

Ex) the field contains:

99966.00

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

回答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)

this will only works for data type Money and 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?

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

回答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?