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

 Select statement to reduce precision? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, November 16, 2006 11:43 AM
 SSC Rookie Group: General Forum Members Last Login: Friday, July 09, 2010 8:01 AM Points: 30, Visits: 18
 Hello all, I'm sure there's a simple answer to this but it's escaping me. How can I reduce the precision of a money or smallmoney datatype in a SELECT statement so that it doesn't show 4 places to the right of the decimal?I tried Select CAST(FIELDNAME) as float, but it will return only one decimal point to the right if the number is a whole number. I want the result to ALWAYS return two decimal places.Thanks
Post #323578
 Posted Thursday, November 16, 2006 11:53 AM
 Hall of Fame Group: General Forum Members Last Login: Wednesday, October 23, 2013 12:46 PM Points: 3,843, Visits: 3,833
 You're going down the right path, but try using decimal data type and setting the precision and scale.  For example,  declare @money moneyset @money = 150.2315select CAST(@money as decimal(18,2)) John Rowan ============================================================================================================ Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #323584
 Posted Thursday, November 16, 2006 11:55 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, January 25, 2010 5:28 PM Points: 1,132, Visits: 42
 I'm not sure what you are going after.  If this is a display issue, you may want to handle this in your front-end.  Otherwise, I do not see the problems you are having and I would absolutely not use "float".  DECLARE @Money TABLE( Price money) INSERT INTO @Money SELECT 1 UNION SELECT 1.01 UNION SELECT 1.001 UNION SELECT 2.5002 UNION SELECT 2.5101 UNION SELECT 3.001 UNION SELECT 4.5202 UNION SELECT 2.515 UNION SELECT 2.5555 SELECT Price, CONVERT( decimal(5,2), Price) AS [ Price 5,2], CONVERT( float, Price) AS [ Price float], ROUND( Price, 2, 1) AS [ Price Round ] FROM @Money I wasn't born stupid - I had to study.
Post #323585
 Posted Thursday, November 16, 2006 2:32 PM
 SSC Rookie Group: General Forum Members Last Login: Friday, July 09, 2010 8:01 AM Points: 30, Visits: 18
 John Rowan, Thanks for that...I forgot about the decimal type. Too bad it returns 0 instead of 0.00. I can handle that in code, thoughFarrell, Yeah, just a display issue. I like dealing with data issues, even if it's just display, in SQL when I can. I can change a stored procedure on the fly without having to stop debugging, change the code, then recompile.Thanks to you both!Mike
Post #323664
 Posted Wednesday, June 19, 2013 6:24 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, June 19, 2013 6:23 AM Points: 1, Visits: 0
 how to reduce precision value
Post #1465124

 Permissions