• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 98
  • Last Modified:

putting commas in numeric value for dollars

I have a field i'm creating

cast (sum(tas.R12Value) as decimal (8,0)) as r12value

which is rounding the number for me fine.
1430022 however
I'm missing the commas ?
would like to see.
1,430,022

Thanks
fordraiders
0
Fordraiders
Asked:
Fordraiders
  • 6
  • 4
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>cast (sum(tas.R12Value) as decimal (8,0)) as r12value
>I'm missing the commas ?
That's because numeric data types such as int, decimal, numeric, etc. only store the number and not any cosmetic formatting such as currency symbols and thousands-separating commas.

Do your formatting in the presentiation (i.e. reporting) layer, not in the data layer.

So ... explain for us where you would 'like to see' the commas.
0
 
FordraidersAuthor Commented:
expressed in dollar amounts
12,000
1,000
13,248,765


etc...
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
That wasn't my question.  Where would you like to see these commas?  In the database, on a report, in an ETL feed, on a boat, with a goat, ...
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
FordraidersAuthor Commented:
dr suess...lol  

on asp.net c#  gridview
0
 
FordraidersAuthor Commented:
this is what I have thus far.

e.Row.Cells[3].Text = String.Format("$ {0:C}", e.Row.Cells[3].Text);
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Ok.  In that case I recommend doing this in the formatting of the gridview.  Since I'm not an expert in either, I'll add those two zones to this question and step back so those experts can read your question and respond.
0
 
SharathData EngineerCommented:
May be this.
convert(varchar,convert(money,sum(tas.R12Value)),1) AS r12value

Open in new window

0
 
FordraidersAuthor Commented:
sharath, perfect,...

Thanks !!
0
 
FordraidersAuthor Commented:
Thanks Sharath and
Jimbo, Thanx for the direction and help !!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split, but I'm going to recommend against the approach sharath posted.   If you convert these numbers to varchar in your data layer, then they will be varchar's in the reporting layer, which means the only way it will be able to participate in math such as addition, subtraction, subtotals, etc. is by implicit conversion, and the characters may (will?) cause it to throw an error.

Better to format in the report.
1
 
FordraidersAuthor Commented:
Jmbo, i tend to agree. Luckily this is just a one time view.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now