[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

Data Type Decimal SQL 2005

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
SeTech
Asked:
SeTech
1 Solution
 
Om PrakashCommented:
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
 
Akin DeluI’m a software engineer, tech enthusiast and UI/UX lover. Commented:
select round(columnname,2)
0
 
gavsmithCommented:
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
 
SeTechAuthor Commented:
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
 
Om PrakashCommented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now