## How to trim the decimal places?

 Author Message Nuts Ten Centuries Group: General Forum Members Points: 1213 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 bitbucket-25253 SSC-Dedicated Group: General Forum Members Points: 34637 Visits: 25280 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 Jeff Moden SSC Guru Group: General Forum Members Points: 504959 Visits: 44238 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs blessybaby SSC Journeyman Group: General Forum Members Points: 85 Visits: 25 Pls try thisDECLARE @Num4 AS DECIMAL(10,4)SET @Num4 = 1234.9876 SELECT STR(@Num4,Len(@Num4),2) Madhivanan-208264 SSCarpal Tunnel Group: General Forum Members Points: 4145 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 manjunath5581 SSC-Enthusiastic Group: General Forum Members Points: 176 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... arun.sas SSCertifiable Group: General Forum Members Points: 6293 Visits: 3493 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 MTV SSC Journeyman Group: General Forum Members Points: 93 Visits: 72 DECLARE @Num4 AS DECIMAL(10,4)SET @Num4 = 1234.982644 SELECT LEFT(CONVERT(VARCHAR(20),@Num4),CHARINDEX('.',CONVERT(VARCHAR(20),@Num4))+2) manjunath5581 SSC-Enthusiastic Group: General Forum Members Points: 176 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. Ramesh Saive SSChampion Group: General Forum Members Points: 14381 Visits: 2649 Using ROUND function...`SELECT CONVERT( NUMERIC(18,2), ROUND(1245.65879, 2, 1) )` --Ramesh