Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 101
  • 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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