Menu
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
522
In SQL Server, you can use the T-SQL FORMAT() function to format a number as a currency. The FORMAT() function allows you to format numbers, dates, currencies, etc. It accepts three arguments; the number, the format, and an optional “culture” argument.
The convert function can put the thousands separator in for you, but only if you are converting from the Money data type to the varchar data type. You also need to use the optional 3rd parameter of the convert function.
The FORMAT() function formats a number to a format like "#,###,###. ##", rounded to a specified number of decimal places, then it returns the result as a string.
'$' + convert(varchar,cast(SalesProducts.Price as money),-1) as Price
This works
173
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!
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
