Solved

Data Type Decimal SQL 2005

Posted on 2010-08-25
5
312 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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

717 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