formatting sql column as currency

TrialUser
TrialUser used Ask the Experts™
on
1) I would like to return a price column as currency $10.50 format. How can I do it in my select statement:

select itemnmbr, price from item

2) would this be faster than doing it in the html using string.format("c", price) after returning the price as such from the sql table

Thanks

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
<  I would like to return a price column as currency $10.50 format. How can I do it in my select statement:

select itemnmbr, price from item

answer

select itemnmbr, cast(price as decimal(10,2)) price from item

Commented:
< would this be faster than doing it in the html using string.format("c", price) after returning the price as such from the sql table
It should be
Top Expert 2011

Commented:
It depends on the server cpu and browser/client  rendering speed


Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Top Expert 2012
Commented:
>>How can I do it in my select statement:<<
This is not something you should do with T-SQL, however if you insist:
If the column has a data type of money or smallmoney then:
SELECT '$' + CONVERT(varchar(20), @YourColumnName, 0)
If you need commas every three digits to the left of the decimal point then:
SELECT '$' + CONVERT(varchar(20), @YourColumnName, 1)
>> would this be faster than doing it in the html using string.format("c", price) after returning the price as such from the sql table

In this case, using sql query will be faster than by doing it at html side.

>> I would like to return a price column as currency $10.50 format. How can I do it in my select statement

As acperkins stated, you need to concate the currency symbol with your currency value.
SELECT '$' + CONVERT(varchar(20), @YourColumnName)
Top Expert 2012

Commented:
>>In this case, using sql query will be faster than by doing it at html side.<<
How can you possibly know this without knowing their code in the front-end?

T-SQL is notoriously slow when doing any type of string manipulation, combine that with the added network traffic generated and I cannot see how that can be true.

But quite aside from the nanosecond you may or may not save, this is a very bad idea and is best relegated to the Presentation layer.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial