Avatar of vbnetcoder
vbnetcoder
 asked on

SQL - truncating to two decimal places

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?
Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
vbnetcoder

8/22/2022 - Mon
Steve Wales

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

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
ASKER CERTIFIED SOLUTION
Kyle Abrahams

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
vbnetcoder

ASKER
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):"
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Steve Wales

I think he means if you have 151.00. Do you want it to display as 151.00 or 151.
dsacker

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. :)
vbnetcoder

ASKER
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...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Steve Wales

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

ASKER
ty