.Net ramblings
# Tuesday, 01 May 2007
SQL: format numeric to x decimal places
i found this was very hard to figure out.  i read lots of approaches to hack the data into char(5) etc to give you "23.54" using Round() etc.  but that seemed messy.  the key is to cast to type Numeric(size, decimalPlaces).  for example:
CONVERT(Numeric(10,2), (Count(ID) * 100. / @Total)) as Percentage

the above will display "23.54" (for example), including rounding, as a number.


Tuesday, 01 May 2007 12:53:15 (GMT Daylight Time, UTC+01:00)  #    Comments [3]  Database

Saturday, 11 July 2009 14:15:02 (GMT Daylight Time, UTC+01:00)
does not work with MS Access sql ... function CONVERT is not defined.
Saturday, 11 July 2009 16:44:58 (GMT Daylight Time, UTC+01:00)
MS Access does not implement standard T-SQL, it has its own limited variation of SQL.
Good luck
Tim
tim
Wednesday, 15 June 2011 18:08:52 (GMT Daylight Time, UTC+01:00)
I need to be able to pass an integer which sets the number of decimal places. It doesn't seem to work to use a variable in the NUMERIC declaration...any ideas?
Eddie
OpenID
Please login with either your OpenID above, or your details below.
Name
E-mail
Home page

Comment (Some html is allowed: a@href@title, strike) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

[Captcha]Enter the code shown (prevents robots):

Live Comment Preview