How to trim the decimal places?

Question

Post reply

How to trim the decimal places?

Viewing 13 posts - 16 through 27 (of 27 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Have you looked up the ROUND() function in BOL yet? I'm pretty sure that this may help you solve your problem.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

In report builder, you may want the function TRUNC instead. Again, though, you will find all of this in BOL.

Books On-Line should be your next best friend (with SSC right there with it).

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 3

[font="Verdana"]I guess the issue is with not wanting to round the decimal places. Sadly, even using the various formatting options in SQL Server Reporting Services will round (I think.)

So it looks like Lynn's suggestion of using the TRUNC() function in SQL Server Reporting Services will do what you want. You can tell it to truncate to 2 decimal places.

Oh, and please please please... do not ever change numeric places by doing string conversions. There's really no need (possibly the most inefficient way I can think of). Between round() and floor() and ceiling(), you don't need to pass it via a string.

For example, you could use:

floor(100*MyValue)/100.0

Anyway, this is largely irrelevent since the place to do it is SQL Server Reporting Services, not in T-SQL.

[/font]

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 4

Bruce W Cassidy (5/20/2009)
[font="Verdana"]I guess the issue is with not wanting to round the decimal places. Sadly, even using the various formatting options in SQL Server Reporting Services will round (I think.)
So it looks like Lynn's suggestion of using the TRUNC() function in SQL Server Reporting Services will do what you want. You can tell it to truncate to 2 decimal places.
Oh, and please please please... do not ever change numeric places by doing string conversions. There's really no need (possibly the most inefficient way I can think of). Between round() and floor() and ceiling(), you don't need to pass it via a string.
For example, you could use:
floor(100*MyValue)/100.0
Anyway, this is largely irrelevent since the place to do it is SQL Server Reporting Services, not in T-SQL.
[/font]

Take Lynn's original advise... The third operand of ROUND will allow you to truncate instead of round. Heh... look it up in Books Online. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

I'm beginning to wondor if the OP has even checked this thread. I haven't seen anything since the original post.

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 6

Jeff Moden (5/20/2009)
Take Lynn's original advise... The third operand of ROUND will allow you to truncate instead of round. Heh... look it up in Books Online. 😉

[font="Verdana"]ROUND() has a third operand? Who knew! That's even better. Thanks Jeff! :-D[/font]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Bruce W Cassidy (5/20/2009)
Jeff Moden (5/20/2009)
Take Lynn's original advise... The third operand of ROUND will allow you to truncate instead of round. Heh... look it up in Books Online. 😉
[font="Verdana"]ROUND() has a third operand? Who knew! That's even better. Thanks Jeff! :-D[/font]

I did, that's why I suggested that OP check out ROUND() in BOL. 😉

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 8

Bruce W Cassidy (5/20/2009)
Jeff Moden (5/20/2009)
Take Lynn's original advise... The third operand of ROUND will allow you to truncate instead of round. Heh... look it up in Books Online. 😉
[font="Verdana"]ROUND() has a third operand? Who knew! That's even better. Thanks Jeff! :-D[/font]

Heh... I use it all the time as a "TRUNC" equivalent but thank Lynn... he was the one trying to get folks to look it up and make their own revelation on that. 😀

That does bring up an interesting sidebar... even if I know a function cold, when I'm working on a non-high pressure job (no such thing as a low pressure job :-P), I'll take the time to revisit the functions I use as a forced refresher. I'm amazed that I still find things that I've previously missed even after writing T-SQL for years and years. Round having a 3rd operand was a revelation for me at one time, too, and I wouldn't have even thought about using it to truncate rather than round if I hadn't gone back to look at it in BOL. It takes time but it's definitely worth it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tatoba.shejal SSC-Addicted Points: 466 More actions · Answer 9

Thanks a lot my problem solved from your given sugestion

Regards,

Tatoba

Regards,
Tatoba

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 10

Thanks for the feedback, Tatoba.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Van Heghe Eddy SSCarpal Tunnel Points: 4449 More actions · Answer 11

Tnx Guys,

This Round (Truncate) was just what i needed today 😉

Wkr,

Eddy

deepaktyagi84 SSC Enthusiast Points: 105 More actions · Answer 12

use Round function with convert......

SELECT convert(DECIMAL(18,2),ROUND(amount,2,1)) FROM #temp

vikingDBA SSCertifiable Points: 5356 More actions · Answer 13

If the objective is to essentially truncate the numbers after the second decimal place (and not actually round the number), use the FLOOR() function. However, it is Friday, and I may be totally misinterpreting the objective......

DECLARE @wow4decimal(10,4)

DECLARE @wow2decimal(10,2)

SET @wow4 = 123.4567

SET @wow2 = FLOOR(@wow4*100) * .01 -- FLOOR gets next lowest integer

PRINT CONVERT(varchar(100),@wow2)

RESULTS:

123.45