SQL - truncating to two decimal places

vbnetcoder
vbnetcoder used Ask the Experts™
on
This query produces stuff like

SELECT
      Round( (SUM(Field1 * 100) / Field2, 2) AS MktPct
FROM Table

12.2300000
1.340000

How would i get rid of the extra zeros after the decimal point?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Steve WalesSenior Database Administrator

Commented:
How about:

SELECT
      CAST(Round( (SUM(Field1 * 100) / Field2, 2) as decimal(18,2)) AS MktPct
FROM Table

(Adjust the 18 to fit how many significant digits you need)
dsackerContract ERP Admin/Consultant

Commented:
Assuming you wouldn't want any non-needed zero (even if it's even in the first or second places after the decimal):

SELECT
      CONVERT(float, Round( (SUM(Field1 * 100) / Field2, 2)) AS MktPct
FROM Table
Senior .Net Developer
Commented:
select CAST (SUM(Field1 * 100) / field2 as numeric(18,2))

cast automatically rounds.
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Author

Commented:
I think that is right.

I don't understand the following quote .. could you give me an example of what could go wrong?

"Assuming you wouldn't want any non-needed zero (even if it's even in the first or second places after the decimal):"
Steve WalesSenior Database Administrator

Commented:
I think he means if you have 151.00. Do you want it to display as 151.00 or 151.
dsackerContract ERP Admin/Consultant

Commented:
Actually, I wrote that before re-reading your heading. You want two decimal places, and I answered as if you would want things like 1.1000000 to look like 1.1 rather than 1.10. The other guys have given you better solutions if you want the cents (2 decimal places) to show. They deserve the points.

You can save the use of FLOAT for something else in the future. :)

Author

Commented:
this is working with the  CONVERT(float,Round( (SUM(t1.[Market Value]) * 100) / TotMktValue, 2, 2)) AS MktPct


How would i change it to use cast ... i keep messing up the syntax...
Steve WalesSenior Database Administrator

Commented:
My response and Kyle's both give you that syntax.  If it is not work, please post the error you are getting

Author

Commented:
ty

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