Format Number to 2 Decimals in MS SQL without Rounding

Introduction

If you wish to Format Number to 2 decimal without rounding off, here is the trick

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 :)