//
you're reading...
Database, SQL Server

Money Format MSSQL SERVER

A reader sent me a question recently in which he wanted toknow how to display currency amounts in the format we commonly refer to ascurrency. (I realize how geo-centric this is, so I will attempt to beworld-centric in my response to the reader’s question.)

Let’s use the Northwind databaseas our testbed. Consider the following SQL request:

USE Northwind
GO
SELECT
    Quantity,
    Unitprice,
    Quantity * UnitPrice AS Amount
FROM [Order Details]

This request results in a set that looks like Listing A (which is an abbreviatedlist). This delivers the correct answers yet not in the desiredformat. We can alter the look of the money columns, but there is a cost ofsorts. For instance, the statement inListing B delivers what we want (i.e., amounts formatted as currency).

The reader also wanted to know how to get the currency sign prepended to the amount, e.g., $1,320.00. Given the revisedstatement in Listing B, this is notdifficult to do, as you can see here:

USE Northwind
GO
SELECT
    Quantity,
    '$' + CONVERT(varchar(12), Unitprice, 1) AS Unitprice,
    '$' + CONVERT(varchar(12), Quantity * UnitPrice, 1) AS Amount
FROM [Order Details]

Listing C showshow this results in the desired display. I should add one additional proviso that isn’t apparent.Style 1 works only on the data types moneyand smallmoney.Therefore, if you ever store your data in another type, then you can do adouble-convert, like this:

CONVERT(varchar, CONVERT(money, YourFieldName), 1)

About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 9 other followers

November 2011
M T W T F S S
    Dec »
 123456
78910111213
14151617181920
21222324252627
282930  

Archives

Web Statistic

Blog Stats

  • 149,580 hits
%d bloggers like this: