Solved

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

Posted on 2016-07-22
6
54 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

752 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