Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

How to trim the decimal places? Expand / Collapse
Author
Message
Posted Wednesday, May 20, 2009 5:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 08, 2013 9:35 AM
Points: 3, Visits: 65
I think that is because of your Decimal declaration. Try this one:-

DECLARE @Num4 AS DECIMAL(30,10)
SET @Num4 = 1567987.4699987
SELECT LEFT(CONVERT(VARCHAR(100),@Num4),CHARINDEX('.',CONVERT(VARCHAR(100),@Num4))+2)

I have changed @Num4 to DECIMAL(30,10) and is working fine.





Post #720461
Posted Wednesday, May 20, 2009 9:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, September 22, 2012 1:54 AM
Points: 4, Visits: 61
Thank you very much for your responses

As i did in Stored procedure is there any way to trim the value to last 2 decimal points in SSRS 2005 reports.

if for example if i have 40524786.406314 i want that to display as

40524789.40.

If i right click on properties and format that to 2 digits it rounds of to

40524789.41

Is there any way to find decimal point and display the right 2 numbers next to the decimal point.

Your help would be greatful..

Thank you
Post #720706
Posted Wednesday, May 20, 2009 10:15 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 22,475, Visits: 30,153
manjunath5581 (5/20/2009)
Thank you very much for your responses

As i did in Stored procedure is there any way to trim the value to last 2 decimal points in SSRS 2005 reports.

if for example if i have 40524786.406314 i want that to display as

40524789.40.

If i right click on properties and format that to 2 digits it rounds of to

40524789.41

Is there any way to find decimal point and display the right 2 numbers next to the decimal point.

Your help would be greatful..

Thank you


Lookup ROUND() in BOL (Books On-Line).



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #720730
Posted Wednesday, May 20, 2009 10:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 375, Visits: 765
From reporting services you should be able to put in the properties of that database field a format of n2. That should format the number for presentation purposes.
Post #720735
Posted Wednesday, May 20, 2009 12:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, September 22, 2012 1:54 AM
Points: 4, Visits: 61
I tried doing that, formatting the value with n2 it rounds to .41 at the end of the sum..

Is there any way to find the decimal point in Expression and trim the 2 numbers right to the decimal point.

if i try to format the number it rounds of my value...
Post #720806
Posted Wednesday, May 20, 2009 12:36 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 22,475, Visits: 30,153
Have you looked up the ROUND() function in BOL yet? I'm pretty sure that this may help you solve your problem.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #720816
Posted Wednesday, May 20, 2009 12:39 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 22,475, Visits: 30,153
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).



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #720817
Posted Wednesday, May 20, 2009 2:03 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 5:55 PM
Points: 957, Visits: 1,027
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.
Post #720882
Posted Wednesday, May 20, 2009 3:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 35,950, Visits: 30,232
Bruce W Cassidy (5/20/2009)
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.

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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #720959
Posted Wednesday, May 20, 2009 3:35 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:07 PM
Points: 22,475, Visits: 30,153
I'm beginning to wondor if the OP has even checked this thread. I haven't seen anything since the original post.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #720976
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse