How can I cast a money field in a view to look like money

I have a special need in a view for a money column to look like money and still be a money datatype. So I need it to look like $100.00 (prefered) or 100.00(can make work).

If I convert like this '$' + CONVERT (NVARCHAR(12), dbo.tblpayments.Amount, 1) it is now a nvarchar and will not work for me.

How can I cast so it is still money? by default the entries look like 100.0000.

They must remain a money datatype.

Try this out.

select'$' +cast (convert (decimal ( 10 , 2 ) , <column name> )as varchar )from <table name>

Hope this will help.|||

This still ends up as a varchar, so it will not work for me.

|||

Is there a way to cast to money and have it only show as 100.00 or can I only cast to decimal(10,2) to do this?

|||

As I have mentioned in my previous, for a similar question from you, what are you doing with the values? If its just for display purpose, use front end formatting functions.

|||

I think I can use the decimal(10,2) to work with my money issue. All though I would prefer that it remain as its original datatype.

I would love to use front end formatting; however, in this case it is not possible.

|||

Then you most likely have a serious design issue.

|||

If you have something constructive to say, please do so. Until you understand someone's underlying requirements you are as ignorant as the in experienced.

|||

If I needed more information to qualify my above statement, I would have asked for it. Fortunately, I don't.

Pointing out that what you are asking for would result in a poor design and you should probably rethink your process rather than implementation is hardly unconstructive. I do however, take offense to your statement, so I will end this conversation here. Good luck on getting help when you insult those around you.

PS. inexperienced is a single word.

|||

Motley,

It was not my intent to insult. What I hope for in these cases is not to be told that I'm wrong, as much as to be offered a constructive suggestion.