Convert Money as String $XX.XX in output

How can I convert or format a Money defined field to look like $xx,xxx.xx?
My data is stored like 50000.99 but I'd like to see $50,000.99 in the query
results. I tried using CAST (SELET CAST (MyField As Money) As MyField FROM
MyTable...) but that didn't seem to change anything.
Thanks!Try this:
SELECT '$' + convert(char(9), MyField , 1)
FROM MyTable
It is not perfect because it will have space between $ and the digit if the
amount is small, and you need to make sure your money amont is less then
100.000. Try it and make more modification as you need.
Perayu
"ShaneFowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23qH6Nm6bGHA.1960@.TK2MSFTNGP05.phx.gbl...
> How can I convert or format a Money defined field to look like $xx,xxx.xx?
> My data is stored like 50000.99 but I'd like to see $50,000.99 in the
> query results. I tried using CAST (SELET CAST (MyField As Money) As
> MyField FROM MyTable...) but that didn't seem to change anything.
> Thanks!
>|||ShaneFowlkes,
See function "convert" in BOL.
Example:
select '$' + convert(varchar(25), cast(50000.99 as money), )
go
AMB
"ShaneFowlkes" wrote:
> How can I convert or format a Money defined field to look like $xx,xxx.xx?
> My data is stored like 50000.99 but I'd like to see $50,000.99 in the query
> results. I tried using CAST (SELET CAST (MyField As Money) As MyField FROM
> MyTable...) but that didn't seem to change anything.
> Thanks!
>
>|||This one is better:
SELECT '$' + convert(varchar(15), MyField , 1)
FROM MyTable.
Perayu
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:eN62z16bGHA.5116@.TK2MSFTNGP02.phx.gbl...
> Try this:
> SELECT '$' + convert(char(9), MyField , 1)
> FROM MyTable
> It is not perfect because it will have space between $ and the digit if
> the amount is small, and you need to make sure your money amont is less
> then 100.000. Try it and make more modification as you need.
> Perayu
> "ShaneFowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
> news:%23qH6Nm6bGHA.1960@.TK2MSFTNGP05.phx.gbl...
>> How can I convert or format a Money defined field to look like
>> $xx,xxx.xx? My data is stored like 50000.99 but I'd like to see
>> $50,000.99 in the query results. I tried using CAST (SELET CAST (MyField
>> As Money) As MyField FROM MyTable...) but that didn't seem to change
>> anything.
>> Thanks!
>|||"ShaneFowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:%23qH6Nm6bGHA.1960@.TK2MSFTNGP05.phx.gbl...
> How can I convert or format a Money defined field to look like $xx,xxx.xx?
> My data is stored like 50000.99 but I'd like to see $50,000.99 in the
query
> results. I tried using CAST (SELET CAST (MyField As Money) As MyField
FROM
> MyTable...) but that didn't seem to change anything.
General note though is this is typically something you want to do in the
front end, not the back end.
But for quick dirty stuff, what others said should work.
> Thanks!
>