Retrieving numeric data upto two decimal values.
-
Hello,
We have several tables that that have fields with data types defined as decimal(n,p) & float. When user retrieves the data, is there a way to round the values up to two decimal values (without explicitly using convert or cast).
Thank you,
R
-
If you mean avoiding using CAST() or CONVERT(), they affect the performance especially when there are a lot of reads and lot of data. We are not planning on changing the schema (re-defining). We would like to keep the precision in the database but only round data to a set precision when retrieved.
What's the difference between columns/fields/attributes? I don't know COBOL.
I think FoxPro has something like SET DECIMAL TO {nDecimalPlaces} and when a SELECT is performed, you get the data rounded to specified precision.
-
The STR command may be helpful.
-
This will not necessarily help performance, but try the ROUND function. Look it up in BOL and you will see how the third parameter can be used...
We too have some datatypes that are floats in the db and should have been decimal(5,2). When you multiply one times another and divide by 100, your variance can be large, especially when many records are summed.
DECLARE @t TABLE( IntegerNumber integer, DecimalNumber decimal(5,1), FloatNumber float)
INSERT INTO @t
SELECT 1, 2.23, 3.333 UNION ALL
SELECT 1, 2.25, 3.666 UNION ALL
SELECT 2, 2.245, 3.945
SELECT ROUND( IntegerNumber, 1) AS IntegerNumber,
ROUND( DecimalNumber, 1) AS DecimalNumber,
ROUND( FloatNumber, 1) AS FloatNumber
FROM @t
SELECT ROUND( IntegerNumber, 0, 1) AS IntegerNumber,
ROUND( DecimalNumber, 0, 1) AS DecimalNumber,
ROUND( FloatNumber, 0 , 1) AS FloatNumber
FROM @t
SELECT ROUND( IntegerNumber, 1, 0) AS IntegerNumber,
ROUND( DecimalNumber, 1, 0) AS DecimalNumber,
ROUND( FloatNumber, 1 , 0) AS FloatNumber
FROM @t
SELECT ROUND( IntegerNumber, 1, 1) AS IntegerNumber,
ROUND( DecimalNumber, 1, 1) AS DecimalNumber,
ROUND( FloatNumber, 1 , 1) AS FloatNumber
FROM @t
I wasn't born stupid - I had to study.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply