SQL Server 2005 Currency Format with comma's and period

Question

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

Answer 1

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

This works

Answer 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!