select statement ???

RinoDM · June 18th, 2008, 03:30 PM

Hi to all ...

in a select statement how can i display a $ in front of a coloum name
of format it to $ 9999.99...

thanking you in advance

Rino

Old Pedant · June 18th, 2008, 03:44 PM

Code:

SELECT '$' + CONVERT( VARCHAR(30), CONVERT( MONEY, yourfield ), 1 )
FROM table
WHERE something = whatever
ORDER BY stuff

???

If yourfield is already a MONEY or SMALLMONEY field, you don't need the inner CONVERT call, but it's not going to hurt to use it.

Old Pedant · June 18th, 2008, 03:45 PM

If you don't want commas in the dollar value:
$9,999.99
then use format 0 instead of 1 for the outer CONVERT call.

See the docs:
http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx

Jeff Moden · June 18th, 2008, 07:05 PM

If you have a GUI... formatting Money, numbers, and dates should all be done by the GUI so the local settings can do it properly. Certainly, you should never store formatted data in the database... not ever. It's as bad as doing an ORDER BY in a view... it just doesn't make sense to store something that's formatted because it's such a nightmare to change if you need to.

--Jeff Moden

Old Pedant · June 18th, 2008, 08:12 PM

Well, one can argue that if the unit of currency in question is US dollars, then there's not much point in formatting in some other fashion.

But never mind, I agree with Jeff Moden. Both in principle and in practice, as I certainly do formatting elsewhere than in the DB.

But Rino asked the question, so I answered it as is.

Jeff Moden · June 18th, 2008, 08:41 PM

Ooohhh... Sorry... wasn't directed at you... Was directed at the OP to try to keep him out of trouble. I know why you did it... same reason I would/have... it's fun ;)

--Jeff Moden

Old Pedant · June 18th, 2008, 08:53 PM

LOL! Yeah, answering questions gets to be an obsession, doesn't it?

RinoDM · June 19th, 2008, 08:40 AM

I put it in my select statement
...'$' + CONVERT( VARCHAR(30), CONVERT( MONEY, yourfield ), 1 )...
and it worked...
Thanks