Solved

Format to Currency with Comma in SQL Server Query

Posted on 2016-10-04
6
105 Views
Last Modified: 2016-10-04
I've been on a formatting binge today with my query! I need to format this to currency but it also needs to include the comma (eg $2,000.00 instead of $2000.00). Right now I have it formatting as $2000.00 but I want the comma included. How can I tweak this to format it with the comma?

CASE WHEN LEFT(dbo.jomast.fjobno, 1) <> 'I' THEN '$' + CONVERT(varchar(20), 
                         CONVERT(money, dbo.sorels.funetprice * dbo.sorels.forderqty)) ELSE '0' END AS ExtPrice

Open in new window

0
Comment
Question by:Lawrence Salvucci
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41828190
Try below

SELECT CONCAT( '$' , REPLACE(CONVERT(varchar(20), (CAST(987654321 AS money)), 1), '.00', '') )

SELECT CONCAT( '$' , FORMAT(CAST(987654321 AS MONEY), N'N', 'en-US') )
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41828195
One more.. if you are using SQL 2012 and above. Easy one.

SELECT FORMAT(1234567.80, '$##,##0.00')
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41828208
What are you building this for?  Reason I ask is because it's a best practice to let the presentation layer handle cosmetic formatting, and the data layer to just store the data.   Also, altering the format will convert the number into a character data type, which means you lose the ability to perform math expressions and aggregates like SUM().

So .. if this is for a report, I HIGHLY ( *** HIGH - LEE *** ) recommend doing the cosmetic formatting in the report and not the database.

If this is for exporting to a file, then the above solutions should work for you.
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41828209
I tried putting one of your first 2 solutions in my query and it added a { fn } to that portion of the query. I replaced your '987654321' with my fields and added the CASE statement from the beginning of my original code. Am I missing something? We are using SQL 2008 so we have to go with one of your first solutions.
0
 
LVL 29

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41828214
Yes the first option will work with SQL Server 2008. that is..


SELECT '$' + REPLACE(CONVERT(varchar(20), (CAST(987654321 AS money)), 1), '.00', '')
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41828218
Jim,
I appreciate your comments and I totally understand where you are coming from. This is actually being exported to a file. Otherwise I wouldn't waste doing all this formatting.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question