Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 18th, 2008, 03:30 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 112
Thanks: 12
Thanked 0 Times in 0 Posts
Default select statement ???

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 June 18th, 2008, 03:44 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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 June 18th, 2008, 03:45 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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
 
Old June 18th, 2008, 07:05 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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 June 18th, 2008, 08:12 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
 
Old June 18th, 2008, 08:41 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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 June 18th, 2008, 08:53 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

LOL! Yeah, answering questions gets to be an obsession, doesn't it?
 
Old June 19th, 2008, 08:40 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 112
Thanks: 12
Thanked 0 Times in 0 Posts
Default

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







Similar Threads
Thread Thread Starter Forum Replies Last Post
select Statement gregalb SQL Server 2000 3 January 15th, 2008 12:00 AM
select statement help... RinoDM SQL Server 2000 13 January 10th, 2008 08:34 PM
Select from another select statement to a repeater simsen ASP.NET 2.0 Professional 0 May 2nd, 2007 04:34 PM
select statement help RinoDM SQL Server 2000 2 January 16th, 2007 11:35 AM
Select Statement jmss66 Oracle 1 May 27th, 2004 02:31 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.