Displaying Fewer Decimal Places

Question

Displaying Fewer Decimal Places

bkmooney

SSCrazy

Points: 2041
More actions
January 8, 2009 at 7:47 am

#203762

I have a query that is compiling hundreds of rows of data to determine performance statistics. While the calculations are correct, they are displaying far more decimal places than is necessary (15 places).
Is there a way to limit all display output to a fixed number of decimals (say 4 or 5) with one command?
Or for each column in my select list would I have to use the CAST() function?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

What are you using to display the results? What are the datatypes of the columns in the calculations? Can you post the query?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

bkmooney SSCrazy Points: 2041 More actions · Answer 2

Here is a simplified version of the query. The actual query contains about a dozen items in the select statement similar to this calculation.

SELECT

Param1

, Param2

, Param3

, EXP(SUM(CASE WHEN(Date BETWEEN '2007-01-01' AND '2008-01-01') THEN (LOG(1+Gains)) ELSE 0 END))-1 as CY2007PercGain

FROM myTable

GROUP BY

Param1

, Param2

, Param3

Param1, Param2, Param3, and Gains are all type DECIMAL(18,8), and Date is type DATETIME

bkmooney SSCrazy Points: 2041 More actions · Answer 3

Oh yeah, I am just running this query in the MS SQL Server Mgmt Studio if that makes a difference.

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 4

investigate the ROUND() function.

~BOT

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

I think using cast or convert is your only option if you are just using SSMS. It is using whatever the data type is for your column, so you could reset that if you don't need that precision, unless you are using float.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jack Corbett SSC Guru Points: 184394 More actions · Answer 6

Oh yeah, forgot about ROUND()

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

bkmooney SSCrazy Points: 2041 More actions · Answer 7

I looked into the ROUND() function as you guys suggested. Maybe my implementation is off because while it rounds the value, it leaves on all the trailing zeros after the rounding takes place.

And since the goal is to make this reader friendly, the trailing zeros aren't helping.

Any ideas how to get rid of these trailing zeros?

DECLARE @Gains AS DECIMAL(18,8)

SET @Gains=RAND()

SELECT

@Gains

, ROUND(@Gains, 4)

Gave me:

0.671448080.67140000

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 8

Aha! Decimal must be immune to a true round::

Try this one!

DECLARE @Gains AS float

SET @Gains=RAND()

SELECT

@Gains

, ROUND(@Gains, 4)

so maybe in your code you could cast to a float and round it.

~BOT

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258