Solved

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

Posted on 2016-07-22
6
34 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
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.

707 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

13 Experts available now in Live!

Get 1:1 Help Now