Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
EugeneLim11
Posting Yak Master
167 Posts |
 Posted - 2008-07-06 : 23:44:32
|
| Dear experts,I have a table named invoice with the following fieldsInvoiceID (GUID)AmountInUSD (money)AmountInForeign (money)Currency (nvarchar(4))Now, I would like to display the amountInUSD, AmountInforeign as part of string with the currency group by invoice.i.e. USD $100 without the decimal point .00 (assume that there is no decimal number, and all entries end with .00) and with a comma (,) for every 3 digits. e.g. USD $30,000 How do I do that?Select InvoiceID, Currency + ' ' + convert(varchar, amountinUSD,1) as AmountInUSD, Currency + ' ' + convert(varchar,amountInForeign, 1) as AmountInForeign group by invoiceId return very close to what I want, except that the decimal places are included as well. How do I get rid of the decimal places and presevere the comma? Thank you.regards,Eugene |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-06 : 23:49:10
|
| This is actually a presentation issue and will be best handled at your front end application. which is your front end? |
 |
|
|
EugeneLim11
Posting Yak Master
167 Posts |
Posted - 2008-07-06 : 23:53:18
|
| ASP.NET. Is there any way I can present the information from the database directly? This is because the same query results is displayed in multiple pages and changing the pages one by one is highly tedious and error prone |
|
|
|
EugeneLim11
Posting Yak Master
167 Posts |
Posted - 2008-07-07 : 01:32:04
|
| Hi, I tried to cast the money as decimal, and change it back to varchar for the concat operation. This works well when I specify that there are no decimal points. But the comma after every 3 digit is lost. Is there any way I can control the formatting of the decimal from MSSQL so that it returns 1. comma for every 3 digits2. no decimal point?in the example above? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-07-07 : 01:40:31
|
you really shouldn't be doing this in SQL.declare @m moneyselect @m = 30000select @m, replace(convert(varchar(10), @m, 1), '.00', '')
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-07-07 : 01:41:31
|
| At the point where you iterate through the results in the .NET application , is where you should format your currency - will you get much more flexibility this wayJack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
|
|
|
EugeneLim11
Posting Yak Master
167 Posts |
Posted - 2008-07-07 : 03:33:33
|
| thank you Jackv, visakh and khtan. Much appreciated. KhTan, is there a big lag in performance when I do it this way? I am wondering why you said that I should not do it this way. I thought that this way is the best as when the query returned is exactly what you want to display, you need not format every page where you display the result. The alternative is to recode lines of code just to format it as what jackv has mentioned, which I think is more inefficient and put more stress on the application server (why you want to add code and overheads to your application when you can get the result formatted nicely as a query in MSSQL?). Secondly, if your result is displayed in more than one page, it means you need to format every single page of your application to the format string, which can add quite a bit of overheads. e.g. if you have 10,000 records, and you need loop through each record to format the data will result in a slow loading page. Jackv is right in having more control over format, but at the expense of having (possibility) longer loading time, and maybe if you forget to format the display in one page, it can result in inconsistency in displaying the information (e.g. USD$ 9,000 in one page and USD$ 9,000.00 in another page). The alternative is to use a OOPs (oriented oriented programming) which has a class to format all the data once, and be reused for the entire web-application. While this is clearly, in my humble opinion, the best method, the new generation of .NET developer has a bad habit of tying Sql Objects to the page as it provides a user -friendly interface. :( In .NET 3.5 framework (Visual Studio 2008), this problem is further complicated by the use of Linq, which encourage programmer to think "backwards". A select statement in Linq will be "FROM table1 where columnName = 'value', SELECT columnName1, columnName2". The data is then encapsulated in a linq class, which makes formatting to take place at the display and not at the data layer class. Wish the guys at microsoft who developed the Linq would know how to make SELECT statements first. |
|
|
|
|
|
|
|
|
money data type problem