SQLServerCentral / SQL Server 2005 / T-SQL (SS2K5) / How to trim the decimal places? / Latest Posts

RE: How to trim the decimal places?

vikingDBA — Fri, 19 Oct 2012 14:00:47 GMT

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 @wow4 decimal(10,4)DECLARE @wow2 decimal(10,2)SET @wow4 = 123.4567SET @wow2 = FLOOR(@wow4*100) * .01 -- FLOOR gets next lowest integerPRINT CONVERT(varchar(100),@wow2)RESULTS:123.45

RE: How to trim the decimal places?

deepaktyagi84 — Thu, 18 Oct 2012 00:03:01 GMT

use Round function with convert......SELECT convert(DECIMAL(18,2),ROUND(amount,2,1)) FROM #temp

RE: How to trim the decimal places?

Van Heghe Eddy — Wed, 17 Oct 2012 04:13:43 GMT

Tnx Guys,This Round (Truncate) was just what i needed today ;-)Wkr,Eddy

RE: How to trim the decimal places?

Jeff Moden — Mon, 17 Aug 2009 08:59:32 GMT

Thanks for the feedback, Tatoba.

RE: How to trim the decimal places?

tatoba.shejal — Mon, 17 Aug 2009 07:09:38 GMT

Thanks a lot my problem solved from your given sugestionRegards,Tatoba

RE: How to trim the decimal places?

Jeff Moden — Thu, 21 May 2009 09:08:40 GMT

[quote][b]Bruce W Cassidy (5/20/2009)[/b][hr][quote][b]Jeff Moden (5/20/2009)[/b][hr]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. ;-)[/quote][font="Verdana"]ROUND() has a third operand? Who knew! That's even better. Thanks Jeff! :-D[/font][/quote]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. :-DThat 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.

RE: How to trim the decimal places?

Lynn Pettis — Wed, 20 May 2009 16:22:10 GMT

RE: How to trim the decimal places?

Bruce W Cassidy — Wed, 20 May 2009 16:04:57 GMT

[quote][b]Jeff Moden (5/20/2009)[/b][hr]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. ;-)[/quote][font="Verdana"]ROUND() has a third operand? Who knew! That's even better. Thanks Jeff! :-D[/font]

RE: How to trim the decimal places?

Lynn Pettis — Wed, 20 May 2009 15:35:12 GMT

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

RE: How to trim the decimal places?

Jeff Moden — Wed, 20 May 2009 15:20:32 GMT

[quote][b]Bruce W Cassidy (5/20/2009)[/b][hr][font="Verdana"]I guess the issue is with [b]not[/b] 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 [b]not[/b] 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:[code]floor(100*MyValue)/100.0[/code]Anyway, this is largely irrelevent since the place to do it is SQL Server Reporting Services, not in T-SQL.[/font][/quote]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. ;-)

RE: How to trim the decimal places?

Bruce W Cassidy — Wed, 20 May 2009 14:03:51 GMT

[font="Verdana"]I guess the issue is with [b]not[/b] 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 [b]not[/b] 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:[code]floor(100*MyValue)/100.0[/code]Anyway, this is largely irrelevent since the place to do it is SQL Server Reporting Services, not in T-SQL.[/font]

RE: How to trim the decimal places?

Lynn Pettis — Wed, 20 May 2009 12:39:13 GMT

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).

RE: How to trim the decimal places?

Lynn Pettis — Wed, 20 May 2009 12:36:03 GMT

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

RE: How to trim the decimal places?

manjunath5581 — Wed, 20 May 2009 12:14:54 GMT

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...

RE: How to trim the decimal places?

dmc-608719 — Wed, 20 May 2009 10:20:39 GMT

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.

RE: How to trim the decimal places?

Lynn Pettis — Wed, 20 May 2009 10:15:30 GMT

[quote][b]manjunath5581 (5/20/2009)[/b][hr]Thank you very much for your responsesAs 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 to40524789.41Is 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[/quote]Lookup ROUND() in BOL (Books On-Line).

RE: How to trim the decimal places?

manjunath5581 — Wed, 20 May 2009 09:44:52 GMT

Thank you very much for your responsesAs 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 to40524789.41Is 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

RE: How to trim the decimal places?

MTV — Wed, 20 May 2009 05:55:37 GMT

I think that is because of your Decimal declaration. Try this one:-DECLARE @Num4 AS DECIMAL(30,10)SET @Num4 = 1567987.4699987SELECT LEFT(CONVERT(VARCHAR(100),@Num4),CHARINDEX('.',CONVERT(VARCHAR(100),@Num4))+2) I have changed @Num4 to DECIMAL(30,10) and is working fine.:-)

RE: How to trim the decimal places?

Ramesh Saive — Wed, 20 May 2009 05:49:44 GMT

Using ROUND function...[code]SELECT CONVERT( NUMERIC(18,2), ROUND(1245.65879, 2, 1) )[/code]

RE: How to trim the decimal places?

manjunath5581 — Wed, 20 May 2009 05:48:10 GMT

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.

RE: How to trim the decimal places?

MTV — Wed, 20 May 2009 05:37:10 GMT

DECLARE @Num4 AS DECIMAL(10,4)SET @Num4 = 1234.982644 SELECT LEFT(CONVERT(VARCHAR(20),@Num4),CHARINDEX('.',CONVERT(VARCHAR(20),@Num4))+2)

RE: How to trim the decimal places?

arun.sas — Wed, 20 May 2009 05:28:49 GMT

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

RE: How to trim the decimal places?

manjunath5581 — Wed, 20 May 2009 04:29:50 GMT

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...

RE: How to trim the decimal places?

Madhivanan-208264 — Fri, 19 Sep 2008 07:59:18 GMT

[quote][b]Nuts (9/18/2008)[/b][hr]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[/quote]Post some sample data with expected result so that we dont need to guess what you really want

RE: How to trim the decimal places?

blessybaby — Fri, 19 Sep 2008 03:38:13 GMT

Pls try thisDECLARE @Num4 AS DECIMAL(10,4)SET @Num4 = 1234.9876 SELECT STR(@Num4,Len(@Num4),2)

RE: How to trim the decimal places?

Jeff Moden — Thu, 18 Sep 2008 22:27:37 GMT

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). :)

RE: How to trim the decimal places?

bitbucket-25253 — Thu, 18 Sep 2008 16:57:25 GMT

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

How to trim the decimal places?

Nuts — Thu, 18 Sep 2008 16:38:31 GMT

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