Money conversion

Hi!

When I write:

'SELECT Amount FROM tTransaktion'

I get returnvalues such as '12000.0000'. Instead, I want it to return '12 000'.

The Amount column is of datatype money. Is this possible!?

Thanks!

select amount, amt, left(amt, len(amt) - 3) as [What you want]
from
(
select amount, replace(convert(varchar(20), amount, 1), ',', ' ') as amt
from
(
select convert(money, 12000) as amount
) a
) b|||

Sure, it's possible using the STR function.

declare @.value money

set @.value = 12000.00

select str(@.value,8,0)

The question is why? It is best to let the user interface handle the display of values and just send back raw values. Also, the money datatype has some issues with roundoff that is covered here: http://www.aspfaq.com/show.asp?id=2503

|||Louis - you're absolutely right -> stupid of me not to let the user interface handle it. Thanks for your answers!|||

Stupid, nah! I still have to fight myself to not format data in SQL since I have control over it and I am addicted to SQL :)

The UI programmers like it too since it is easier on them, but they are also the ones that argue about scalability of the database server, so offloading CPU work like this to them is easier to justify. Plus, you can use the regional settings of the client to display the data as they desire (which is a good thing too.)

|||

If you need commas you can use this

declare @.value money

set @.value = 12000.00

select convert(varchar,@.value,1)

Denis the SQL Menace

http://sqlservercode.blogspot.com/