-1

I have data in my sql database like 645.000 and i need to format it to include currency symbols e.g., $645.000

How can I achieve this in SQL?

thejartender
  • 9,339
  • 6
  • 34
  • 51
Suresh Chaudhary
  • 1,609
  • 5
  • 25
  • 40
  • Can you post some information about your table structure? – Aaron Newton Feb 03 '11 at 08:34
  • ya sure. i have a one Product Table which have one "BCWS" column with money datatype. in this column i have data like 456.000,331500000.0000,854.000 etc. i want those data in $ format using sql server. – Suresh Chaudhary Feb 03 '11 at 08:37
  • chaudhary: **please**, don't put stuff like that into a comment - it's really really hard to read. Please instead **update** your original question by **editing** it and provide that additional info - thanks! – marc_s Feb 03 '11 at 09:31

4 Answers4

5

You don't need to and should not be formatting it in SQL Server - instead it's your application that needs to format it for the UI.

You didn't say what your application is coded in, e.g. in C# we could use

Label1.Text = string.Format("Amount is {0:c}", amount);

Then you can be sure that not only will it use the correct currency symbol, it will also use the correct decimal and thousands separator symbols.

JK.
  • 21,477
  • 35
  • 135
  • 214
1

SQL server has no control over how numbers are displayed in your client application. Modify the application settings, Windows control panel or your program code to change the way numbers are displayed.

nvogel
  • 24,981
  • 1
  • 44
  • 82
0

what is the datatype of your column?

In general currency prefixes should be added only in the UI (User Interface) and at the database level you should work just with numbers. Best data type to store money values is MONEY.

Davide Piras
  • 43,984
  • 10
  • 98
  • 147
  • Are you sure that MONEY is the "best" data type to store a monetary amount? Why? I've never found a use for it and in some calculations money yields very inaccurate results compared to NUMERIC. On the other hand MONEY does have some performance advantages over NUMERIC and may save a small amount of storage. I'm just not sure that makes it worth saying it's the "best" type for money values. – nvogel Feb 03 '11 at 09:15
  • sorry dportas I was probably a bit too fast in my post, money has some advantages but I cannot say now, without further reading, if money is absolutely the best in all cases, actually there are posts in which people say to do not use it. – Davide Piras Feb 03 '11 at 09:22
0

Refer this if you really need to do this from the database rather than from the UI.

http://www.java2s.com/Code/SQLServer/Data-Type/Formatmoneycurrency.htm

Nipuna
  • 6,846
  • 9
  • 64
  • 87
  • i got the this error: "There is insufficient result space to convert a money value to varchar." if my value is :22888360.0000 – Suresh Chaudhary Feb 03 '11 at 08:47
  • The problem is with your VARCHAR size. make it a larger value (VARCHAR(20) or something) and try. – Nipuna Feb 03 '11 at 08:50
  • ya thats righ but i want to format in this way: $228,883,60. using your example , i got the data like: $22888360.00. – Suresh Chaudhary Feb 03 '11 at 08:59
  • I suggest you don't convert the number to a string. It makes more sense to store and process monetary amounts as numeric values. – nvogel Feb 03 '11 at 09:05
  • Agree with dportas. In the database level you better keep your numerics as numerics rather than converting to string. You can easily do this at GUI. – Nipuna Feb 03 '11 at 09:12