Search This Blog & Web

Monday, February 21, 2011

Truncate Extra 0's after 2 Decimal Points

Recently I have faced a problem and spent few hours to find its solution.

Problem : Using Database in financial applications. Sometimes there are requirements for division of columns. Like finding Daily transaction summery or finding Percentage of some values etc. During this division SQL SERVER returns data with '0' as postfix even if you apply round() function. But there are requirements to show data till  2 decimal places.

Solution : There are multiple solutions for this problem but most easy way i have found is to declare a column or convert the column to a float value. It will return decimal values with respect to Round() function.

Example : Find the Attached Image as an Example

I have Declared a table variable and I have column data types as Money and Float respectively. During insertion of Data i have inserted different values in both column which have decimal values up to 2 and more. As you can see during Insertion to a fixed value DecimalDivide column returns data up to 2 decimal and many 0 Zeros leading to the value. But FloatDivide column returns data only to 2 decimal places.

No comments: