Solved

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

Posted on 2016-07-22
6
40 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 65

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 250 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 29

Assisted Solution

by:Olaf Doschke
Olaf Doschke earned 250 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 142

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 29

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

929 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now