Solved

Format to Currency with Comma in SQL Server Query

Posted on 2016-10-04
6
42 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
  • 3
  • 2
6 Comments
 
LVL 28

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 28

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 65

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 28

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
I have a large data set and a SSIS package. How can I load this file in multi threading?
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

778 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