Solved

Data Type Decimal SQL 2005

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL USE DATABASE VARIABLE 5 30
Where is the Help Section? 8 29
MS SQL SERVER and ADODB.commands 8 23
SQL Recursion schedule 13 19
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

820 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