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

CMChalcraft
CMChalcraft used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
<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')
Senior Software Developer
Commented:
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.
Olaf DoschkeSoftware Developer
Commented:
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.
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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.
Olaf DoschkeSoftware Developer

Commented:
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.
CMChalcraftFinance Director

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial