Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 How to trim the decimal places? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, September 18, 2008 4:38 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, September 30, 2010 8:02 PM Points: 155, Visits: 215
 Hi GuysI have a column with the figures which have 4 decimal points.I want to trim them to two decimal points. How do I do it??Can it be done fron the reporting services as well??Thanks
Post #572191
 Posted Thursday, September 18, 2008 4:57 PM
 SSCertifiable Group: General Forum Members Last Login: Sunday, January 4, 2015 7:55 AM Points: 5,333, Visits: 25,280
 You could use the CONVERT function for example:DECLARE @Num4 AS DECIMAL(10,4)DECLARE @Num2 AS DECIMAL(10,2)SET @Num4 = 1234.9876 SET @Num2 = CONVERT(DECIMAL(10,2),@Num4)SELECT @Num2@Num2 will then equal 1234.99 -- note the rounding which has taken place.of course this works as wellDECLARE @Num4 AS DECIMAL(10,4)DECLARE @Num2 AS DECIMAL(10,2)SET @Num4 = 1234.9876 SET @Num2 = @Num4SELECT @Num2@Num2 will then equal 1234.99 -- note the rounding If everything seems to be going well, you have obviously overlooked something. RonPlease help us, help you -before posting a question please read Before posting a performance problem please read
Post #572195
 Posted Thursday, September 18, 2008 10:27 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 2:41 PM Points: 42,081, Visits: 39,473
 Much simpler than all that... check out the ROUND function or the STR function (which also right justifies if you need that for a report or file). :) --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." Helpful Links:How to post code problemsHow to post performance problems
Post #572250
 Posted Friday, September 19, 2008 3:38 AM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, January 23, 2013 2:51 AM Points: 29, Visits: 25
 Pls try thisDECLARE @Num4 AS DECIMAL(10,4)SET @Num4 = 1234.9876 SELECT STR(@Num4,Len(@Num4),2)
Post #572359
 Posted Friday, September 19, 2008 7:59 AM
 Old Hand Group: General Forum Members Last Login: Friday, June 26, 2015 6:44 AM Points: 329, Visits: 476
 Nuts (9/18/2008)Hi GuysI have a column with the figures which have 4 decimal points.I want to trim them to two decimal points. How do I do it??Can it be done fron the reporting services as well??ThanksPost some sample data with expected result so that we dont need to guess what you really want MadhivananFailing to plan is Planning to fail
Post #572541
 Posted Wednesday, May 20, 2009 4:29 AM
 Forum Newbie Group: General Forum Members Last Login: Saturday, September 22, 2012 1:54 AM Points: 4, Visits: 61
 Hi,I want to trim the value of decimal point to 2.For example1245.658797123597.599945798754.589785787561.124657I want this to be displayed as1245.65123597.59798754.58787561.12If i try to use decimal(15,2) it will round off the value.If i convert to type money then also the values get round offThe value type is real in the databasePlease help...
Post #720424
 Posted Wednesday, May 20, 2009 5:28 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, July 25, 2016 5:45 AM Points: 1,229, Visits: 3,493
 hi,try this,create table #temp(amount decimal(15,6))insert into #tempselect 1245.658797unionselect 123597.599945unionselect 798754.589785unionselect 787561.124657select substring((cast(amount as char)),0,(charindex('.',(cast(amount as char)))+3)) amount from #tempamount1245.65123597.59787561.12798754.58ARUN SAS
Post #720442
 Posted Wednesday, May 20, 2009 5:37 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, December 11, 2015 2:41 AM Points: 3, Visits: 72
 DECLARE @Num4 AS DECIMAL(10,4)SET @Num4 = 1234.982644 SELECT LEFT(CONVERT(VARCHAR(20),@Num4),CHARINDEX('.',CONVERT(VARCHAR(20),@Num4))+2)
Post #720447
 Posted Wednesday, May 20, 2009 5:48 AM
 Forum Newbie Group: General Forum Members Last Login: Saturday, September 22, 2012 1:54 AM Points: 4, Visits: 61
 Hi Madhu,When my value is like 1567987.4699987 i got the output as 1567987.47 which is actually rounding off the valueDECLARE @Num4 AS DECIMAL(10,4)SET @Num4 = 1567987.4699987SELECT LEFT(CONVERT(VARCHAR(20),@Num4),CHARINDEX('.',CONVERT(VARCHAR(20),@Num4))+2) And the values specified above are just examples not real values. Hope i am clear in my explaining the problem.
Post #720458
 Posted Wednesday, May 20, 2009 5:49 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, June 7, 2016 5:15 AM Points: 2,562, Visits: 2,643
 Using ROUND function...`SELECT CONVERT( NUMERIC(18,2), ROUND(1245.65879, 2, 1) )` --Ramesh
Post #720459

 Permissions