<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
T-SQL (SS2K5)
»
How to trim the decimal places?
28 posts, Page 2 of 3
««
1
2
3
»»
How to trim the decimal places?
Rate Topic
Display Mode
Topic Options
Author
Message
MTV
MTV
Posted Wednesday, May 20, 2009 5:55 AM
Forum Newbie
Group: General Forum Members
Last Login: Thursday, May 02, 2013 3:58 AM
Points: 3,
Visits: 61
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
manjunath5581
manjunath5581
Posted Wednesday, May 20, 2009 9:44 AM
Forum 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
Lynn Pettis
Lynn Pettis
Posted Wednesday, May 20, 2009 10:15 AM
SSC-Insane
Group: General Forum Members
Last Login: Yesterday @ 11:17 PM
Points: 21,617,
Visits: 27,450
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
dmc-608719
dmc-608719
Posted Wednesday, May 20, 2009 10:20 AM
Old Hand
Group: General Forum Members
Last Login: Monday, April 23, 2012 7:35 AM
Points: 373,
Visits: 761
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
manjunath5581
manjunath5581
Posted Wednesday, May 20, 2009 12:14 PM
Forum 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
Lynn Pettis
Lynn Pettis
Posted Wednesday, May 20, 2009 12:36 PM
SSC-Insane
Group: General Forum Members
Last Login: Yesterday @ 11:17 PM
Points: 21,617,
Visits: 27,450
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
Lynn Pettis
Lynn Pettis
Posted Wednesday, May 20, 2009 12:39 PM
SSC-Insane
Group: General Forum Members
Last Login: Yesterday @ 11:17 PM
Points: 21,617,
Visits: 27,450
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
Bruce W Cassidy
Bruce W Cassidy
Posted Wednesday, May 20, 2009 2:03 PM
SSC Eights!
Group: General Forum Members
Last Login: Friday, March 22, 2013 11:49 AM
Points: 945,
Visits: 998
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
Jeff Moden
Jeff Moden
Posted Wednesday, May 20, 2009 3:20 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 32,906,
Visits: 26,792
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #720959
Lynn Pettis
Lynn Pettis
Posted Wednesday, May 20, 2009 3:35 PM
SSC-Insane
Group: General Forum Members
Last Login: Yesterday @ 11:17 PM
Points: 21,617,
Visits: 27,450
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 »
28 posts, Page 2 of 3
««
1
2
3
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
