?
Solved

How do I get a numeric field to output to only 2 decimal points

Posted on 2016-07-22
6
Medium Priority
?
71 Views
Last Modified: 2016-07-25
How do I get a numeric field to output to only 2 decimal points, such as 4235.98? With Net being the value field that current outputs 64.3232697. I only want 64.32 to be the result.

SELECT ID, HOMENAME, Net
0
Comment
Question by:CMChalcraft
6 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41724845
<Knee-jerk reaction>

SELECT ID, HOMENAME, CAST(Net as numeric(19,2)) as Net
FROM whatever

The 19 number is the total number of digits, and can be decreased to best represent the values in that column to minimize the size needed to store them. (see 'Storage Bytes')
0
 
LVL 20

Accepted Solution

by:
Russ Suter earned 1000 total points
ID: 41724846
The simplest solution would be to cast the field to a DECIMAL data type with the correct number of decimal places like this:
cast(SomeValue as decimal(19,2)) as SomeAlias

Open in new window

That will round your value to 2 decimal places.
0
 
LVL 30

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 1000 total points
ID: 41724860
I suggest you rather do this formatting to two decimal places client side.

C#: String s = String.Format("The current price is {0:C2} per ounce.",pricePerOunce);
PHP: echo money_format("The price is %i", $number);
JS: var profits=2489.8237; profits = profits.toFixed(2);
...

The job of the server is to serve the data and the job of the frontend to display it user friendly with user locale, etc. That's separate concerns.

Bye, Olaf.
1
Evaluating UTMs? Here's what you need to know!

Evaluating a UTM appliance and vendor can prove to be an overwhelming exercise.  How can you make sure that you're getting the security that your organization needs without breaking the bank? Check out our UTM Buyer's Guide for more information on what you should be looking for!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41724864
i agree with the above.
if you don't want to round, but floor/ceil instead, you will need to *100 and /100 in betwee.
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 41724869
Another idea would be to convert to an int price in pence/cent/whatever and fill in the decimal place at the frontend again. The advantage being you transfer only exact int values. On the other hand there is the currency type for a reason.

Bye, Olaf.
0
 

Author Closing Comment

by:CMChalcraft
ID: 41727442
Thanks for rapid help. I will llok at Olaf suggests when I have more time. Have used Russ's solution as quicker to implement.

Regards

Chris C
0

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question