Format a money field in SQL without converting to varchar?

Question

I need to be able to display a money field as $XX,XXX.XX, but without converting to varchar using total_eval = '$' + CONVERT(varchar(19),total_eval.opvValueMoney,1)

My project uses sorting of the information after I pull this to sort the column and it doesn't sort correctly when the column is a varchar.

Is there anyway to do this?

This is part of an ASP.NET system, but I have no access or control over after the information is returned.

Answer 1

Can you not format this when the data is being printed on the screen? Then the number remains a number and you can format is as you please at the presentation level.

For example, using PHP you could do something like this:

echo money_format('$%i', 3.4); // echos '$3.40'
//                       ^ here is your number, no formatting from the db!

This example was found in an answer to this question.