Technical Article

Exactly 2 digits after decimal without Rounding

,

If you wish to Format Number to 2 decimal without rounding it then, here is the trick.
This is also useful when you are dividing 2 numbers and you need answer in 2 digiits only without rounding


Create Table in sql 

CREATE TABLE [Table1] (
 [area] [float] NULL 
) 

  

Insert Some data 

INSERT INTO Table1 ([area]) VALUES(12.693)
INSERT INTO Table1 ([area]) VALUES(1256.12963)
INSERT INTO Table1 ([area]) VALUES(25.998596)
INSERT INTO Table1 ([area]) VALUES(1.963) 

  

Now the query-

select 
cast(cast(area as int) as varchar(10)) + cast(substring(cast(area-cast(area as int) as varchar(10)),2,3) as varchar(4)),
FROM Table1


Logic: 

1) Convert number into integer 

2) Subtract interger part from actual number  

3) Convert answer from step 2 to varchar and substring it to take 3 digits including decimal 

4) Concate (+) step 1 and 2 

Happy SQLing :)

Rate

1 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

1 (1)

You rated this post out of 5. Change rating