Solved

Data Type Decimal SQL 2005

Posted on 2010-08-25
5
307 Views
Last Modified: 2012-05-10
I have a table in a SQL 2005 data base that has a couple of decimal type colulmns in it.  I  use these columns to develop a calculation in another new column and the output goes to 12 plus decimal places. My questions is how can I have the output only show to 2 decimal places. I know that when you set up a table you can set the data type of a column to decimal (4,2) for 4 places precision and 2 places output, but this is a calculated colulmn.
thanks
0
Comment
Question by:SeTech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 22

Expert Comment

by:Om Prakash
ID: 33519760
you can convert the calculated field to decimal
Example:
select field1, convert(decimal(14,2), field1*12) from mytest
where field1 is decimal in table.
0
 
LVL 5

Expert Comment

by:Akin Delu
ID: 33519851
select round(columnname,2)
0
 
LVL 10

Expert Comment

by:gavsmith
ID: 33519877
Are you performing the calculation progrmatically or via a SQL statement? And do you need to store more than 2 decimal places and only output 2?

Solution 1 would be round to 2 decimal places before you store your answer i.e
set CalculatedColumn= round(Col1 * Col2 / Col3.. , 2)

Solution 2 would be to store the whole value and only output 2 decimal places but again this depends on how you are outputting the value, you could create a view and round at the SQL select i.e
SELECT ROUND(CalculatedColumn, 2) FROM SomeTable

HTH
0
 

Author Comment

by:SeTech
ID: 33520348
I actually had used the Round () function as Round ( Col1/ Col2,2) and that does produce the results I want (eg 46.23) my problem  (I should have been more clear in my first note) is that the 46.23 shows as 46.230000000000. And I should have asked how to eliminate the trailing 0's.
Thanks Much
0
 
LVL 22

Accepted Solution

by:
Om Prakash earned 500 total points
ID: 33520444
Check the following:
create table mytest (field1 decimal (15,12))
insert into mytest values (46.23)
select * from myTest
--will give you following
46.230000000000
select  convert(decimal(15,2), field1) from mytest
--will give you following
46.23
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question