Can't Convert a Convertible - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Can't Convert a Convertible

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2008-09-23 : 17:05:32

I have a problem getting amounts to look like money amounts.

Here's my example...

--Editing Automobiles
DROP TABLE #Automobiles
CREATE TABLE #Automobiles
(_Vehicle nvarchar (25) NULL,
_Price nvarchar (20) NULL
)
GO

INSERT #Automobiles VALUES ('1955 DESOTO FIREFLITE ','17888.4400')
INSERT #Automobiles VALUES ('1959 OLDSMOBILE DELTA 88','13,555.1300')
INSERT #Automobiles VALUES ('1967 CHEVROLET IMPALA ','9,777.0')
INSERT #Automobiles VALUES ('1968 CHRYSLER IMPERIAL ','11.444.8800')
GO

SELECT * FROM #Automobiles
GO

SELECT _Vehicle, CONVERT(decimal(12,2),_Price) as SALE_PRICE
FROM #Automobiles
WHERE _Price > 9999.99
ORDER BY _Price DESC
GO

How can I get the amounts to come out with a $ and commas every three digits?

Thanks

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-09-23 : 17:15:48

That is usually something you would do in a front end reporting application. Do you really need to do this in SQL?

CODO ERGO SUM

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-23 : 18:05:54

To Echo what MJV said, this should be done in the front end. Additionally, you should use the proper data types.

Does you data acuall look like that (11.444.8800) or is that just a typo? If it's just a typo, maybe this will work for you:

SELECT _Vehicle, '$' + CONVERT(VARCHAR(30), CAST(_Price AS MONEY), 1) as SALE_PRICE
FROM @Automobiles
WHERE CAST(_Price AS MONEY) > $9999.99
ORDER BY CAST(_Price AS MONEY) DESC

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2008-09-24 : 15:34:52

Yes....that's the way the data is being given to me. Normally, I'd normalize, but I can't so I came to you all for assistance.

Yes, I REALLY Need to do this SQL.....Why else would I be asking?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-09-24 : 16:52:14

Not going to be pretty. Feel free to come up with a better looking solution if you don't like mine.


select
	*,
	[MyMoney] =
	'$'+
	case
	when n < 1000.00
	then convert(varchar(20),n)
	when n < 10000.00
	then stuff(convert(varchar(20),n),2,0,',')
	when n < 100000.00
	then stuff(convert(varchar(20),n),3,0,',')
	when n < 1000000.00
	then stuff(convert(varchar(20),n),4,0,',')
	when n < 10000000.00
	then stuff(stuff(convert(varchar(20),n),5,0,','),2,0,',')
	when n < 100000000.00
	then stuff(stuff(convert(varchar(20),n),6,0,','),3,0,',')
	when n < 1000000000.00
	then stuff(stuff(convert(varchar(20),n),7,0,','),4,0,',')
	else null
	end 
from
	( -- Test data
	select n = convert(numeric(12,2),0.00)	union all
	select n = 9.00	union all
	select n = 99.00	union all
	select n = 999.00	union all
	select n = 9999.00	union all
	select n = 99999.00	union all
	select n = 999999.00	union all
	select n = 9999999.00	union all
	select n = 99999999.00	union all
	select n = 999999999.00
	) a


Results:
n              MyMoney                 
-------------- ----------------------- 
.00            $0.00
9.00           $9.00
99.00          $99.00
999.00         $999.00
9999.00        $9,999.00
99999.00       $99,999.00
999999.00      $999,999.00
9999999.00     $9,999,999.00
99999999.00    $99,999,999.00
999999999.00   $999,999,999.00

(10 row(s) affected)

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-10-15 : 16:42:54

Enhanced to handle negative numbers.


select
	*,
	[MyMoney] =
	case when n < 0 then '-$' else '$' end+
	case
	when abs(n) < 1000.00
	then convert(varchar(20),abs(n))
	when abs(n) < 10000.00
	then stuff(convert(varchar(20),abs(n)),2,0,',')
	when abs(n) < 100000.00
	then stuff(convert(varchar(20),abs(n)),3,0,',')
	when abs(n) < 1000000.00
	then stuff(convert(varchar(20),abs(n)),4,0,',')
	when abs(n) < 10000000.00
	then stuff(stuff(convert(varchar(20),abs(n)),5,0,','),2,0,',')
	when abs(n) < 100000000.00
	then stuff(stuff(convert(varchar(20),abs(n)),6,0,','),3,0,',')
	when abs(n) < 1000000000.00
	then stuff(stuff(convert(varchar(20),abs(n)),7,0,','),4,0,',')
	else null
	end 
from
	( -- Test data
	select	distinct
		n = aa.n*bb.neg
	from
		( 
		select n = convert(numeric(12,2),0.00)	union all
		select n = 9.00	union all
		select n = 99.00	union all
		select n = 999.00	union all
		select n = 9999.00	union all
		select n = 99999.00	union all
		select n = 999999.00	union all
		select n = 9999999.00	union all
		select n = 99999999.00	union all
		select n = 999999999.00
		) aa
		cross join
		(
		select neg = 1 union all select neg = -1
		) bb
	) a
order by
	n

results:


n                         MyMoney                  
------------------------- ------------------------ 
-999999999.00             -$999,999,999.00
-99999999.00              -$99,999,999.00
-9999999.00               -$9,999,999.00
-999999.00                -$999,999.00
-99999.00                 -$99,999.00
-9999.00                  -$9,999.00
-999.00                   -$999.00
-99.00                    -$99.00
-9.00                     -$9.00
.00                       $0.00
9.00                      $9.00
99.00                     $99.00
999.00                    $999.00
9999.00                   $9,999.00
99999.00                  $99,999.00
999999.00                 $999,999.00
9999999.00                $9,999,999.00
99999999.00               $99,999,999.00
999999999.00              $999,999,999.00

(19 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources