5

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
RPS
  • 1,401
  • 8
  • 19
  • 32
  • 6
    Why would you want to do that? The data layer should not bother to format data for the presentation layer... – Lucero Nov 24 '10 at 17:15

4 Answers4

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

This works

Ajay
  • 6,418
  • 18
  • 79
  • 130
RPS
  • 1,401
  • 8
  • 19
  • 32
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!

Jade
  • 2,972
  • 1
  • 11
  • 9
1

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

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

gbn
  • 422,506
  • 82
  • 585
  • 676
0

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?

KingRider
  • 2,140
  • 25
  • 23