Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Format to Currency with Comma in SQL Server Query

Posted on 2016-10-04
6
Medium Priority
?
152 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 30

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 30

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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 30

Accepted Solution

by:
Pawan Kumar earned 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

722 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