Exact value after decimal without Rounding Off in SQL

 Exact values after decimal without Rounding Off in SQL

When Writing the sql code i was performing calculation in sql query and the values were coming in decimal 

upto 4 and 5 places of Decimal .So the requirement was to show only 2 values after decimal point without rounding off.

Eg  2.8988 it should not be round off to 2.90 it should show only 2.89 that was the requirement.

Converting the values to any datatype was not giving the exact result. All were rounding off the values to 2 decimal place  like below 

--converting to decimal

select convert(decimal(10,2),2.8988)--2.90

---converting to numeric

select convert(numeric(10,2),2.8988)--2.90

--Round Function

select round(2.8988,2) --2.90

If you will see above three queries all were rounding off the values which was not meeting the requirement

So Here is the Solution

select LEFT('2.8988',CHARINDEX('.',2.8988,0))+ SUBSTRING('2.8988',CHARINDEX('.',2.8988,0)+1,2) 

Output 2.89

This is what was required 

below is the test table to use above query in table

create table Numbertest

(Id int identity  (1,1),Number float )

insert into numbertest values(2.8883),(2.87877),(555.43434),(2121.32),(0.3232)

select LEFT(Number,CHARINDEX('.',Number,0))+ SUBSTRING(cast(Number as varchar),CHARINDEX('.',Number,0)+1,2) as Number

from numbertest

output--Exact two Values after decimal without Rounding off

Number

2.88

2.87

555.43

2121.32

0.32

The Reason i cast the number to varchar is substring function works with string ie nvarchar field

if i directly write the column name Number which is of float data type it will throw an error

.

--without casting to varchar throwing error

select LEFT(Number,CHARINDEX('.',Number,0))+ SUBSTRING(number,CHARINDEX('.',Number,0)+1,2)

from numbertest

Msg 8116, Level 16, State 1, Line 2

Argument data type float is invalid for argument 1 of substring function.