[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Data Type Decimal SQL 2005

Posted on 2010-08-25
Medium Priority
322 Views
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
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

LVL 22

Expert Comment

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

ID: 33519851
select round(columnname,2)
0

LVL 10

Expert Comment

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

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

Om Prakash earned 2000 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

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
###### Suggested Courses
Course of the Month14 days, 8 hours left to enroll