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

 Remove Decimals Without Rounding Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, September 26, 2007 12:35 PM
 Forum Newbie Group: General Forum Members Last Login: Monday, December 8, 2014 6:45 AM Points: 5, Visits: 21
 How do I take a number like maybe 10.73 and remove the decimal place and making it a number like 1073. In other words I have to remove the decimal. I need to do it using T-SQL Thanks for any help Ted
Post #403180
 Posted Wednesday, September 26, 2007 1:19 PM
 SSC Rookie Group: General Forum Members Last Login: Tuesday, October 7, 2008 11:53 AM Points: 46, Visits: 85
 declare @mynum decimal(10,2) declare @mynum1 int set @mynum = 10.73 select @mynum1 = @mynum * 100 print @mynum print @mynum1 or declare @mynum decimal(10,2) set @mynum = 10.73 select cast(@mynum * 100 as int)
Post #403197
 Posted Wednesday, September 26, 2007 1:54 PM
 Forum Newbie Group: General Forum Members Last Login: Monday, December 8, 2014 6:45 AM Points: 5, Visits: 21
 Thanks for your prompt answer. I was looking for something that would get rid of the decimal point if the number was 10.73 or 10.733 or 10.7333 or 10.7 The decimal place being in any position Thanks Ted
Post #403210
 Posted Wednesday, September 26, 2007 2:10 PM
 SSC Rookie Group: General Forum Members Last Login: Tuesday, October 7, 2008 11:53 AM Points: 46, Visits: 85
 what is the datatype of the field containing the decimal point? If it's varchar you could: declare @mynum varchar(20) set @mynum = '10.7333' select replace(@mynum,'.','')
Post #403217
 Posted Wednesday, September 26, 2007 3:04 PM
 Forum Newbie Group: General Forum Members Last Login: Monday, December 8, 2014 6:45 AM Points: 5, Visits: 21
 The field is numeric(5,4) but you have something there. would it be possible to move it to a char field and then apply the logic you gave me Again, thank you so much for your generous help Ted
Post #403236
 Posted Wednesday, September 26, 2007 3:29 PM
 SSC Veteran Group: General Forum Members Last Login: Thursday, February 4, 2016 2:58 PM Points: 265, Visits: 188
 declare @i decimal(10,2) Set @i = 1078.734 select (left(@i, Charindex('.', @i, 1) - 1 )) ******************Dinakar NethiLife is short. Enjoy it.******************
Post #403240
 Posted Thursday, September 27, 2007 12:02 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 3:23 PM Points: 42,036, Visits: 39,415
 Doesn't matter if it's varchar or not... your method still works... declare @i decimal(10,2) Set @i = 1078.734 PRINT REPLACE(@i,'.','') --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 #403351
 Posted Thursday, September 27, 2007 3:58 PM
 Forum Newbie Group: General Forum Members Last Login: Monday, December 8, 2014 6:45 AM Points: 5, Visits: 21
Post #403809
 Posted Thursday, September 27, 2007 7:47 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 3:23 PM Points: 42,036, Visits: 39,415
 Cool... but it's customary to thank folks by posting the code that you used to make it work ;) --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 #403860
 Posted Friday, September 28, 2007 12:19 PM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Sunday, March 22, 2015 4:48 PM Points: 590, Visits: 787
 The problem with using the Replace string function is that 10.341 ends up as 1034100 if the numeric is defined with five places. If you want 10.342 to end up as 10341, 10.73 as 1073, 10.733 as 10733, 10.7333 as 107333 and 10.7 as 107, then the best way is to use numeric processes rather than string. The only thing is, you have to define your working number to have enough places to the left to contain the entire value. So your example of storing 10.341 into a variable defined as decimal(5,4) is bogus--it only has one place to the left of the decimal point. So if your value is defined as "decimal(x,y)" then you have to declare a working variable as "decimal(x+y,y)" to contain the entire finished value. ```declare @Original decimal( 10, 5 ), @Working decimal( 15, 5 ), -- 10 + 5 = 15 @Result decimal( 15, 0 ); -- Doesn't need scale, only precision Set @Original = 1078.734; -- This would be, say, an input parameter -- First, make a copy into the working variable capable of handling it. Set @Working = @Original; -- Now set up the loop Set @Result = Floor( @Working ); While @Result < @Working begin Set @Working = @Working * 10; Set @Result = floor( @Working ); end--while select @Result as Result, @Working as Working;```The loop executes one time through for each significant digit to the right of the decimal point -- in this example, three times. The result is 1078734 instead of 107873400. As an aside, does anyone know how to get the old "
" formatting back? This code IFCode shortcut sucks. Sure, the code goes into a nice text field :) but everything is double spaced.  								 				Tomm Carr--Version Normal Form -- http://groups.google.com/group/vrdbms
Post #404193

 Permissions